[Laravel 5.6] How to Fix SQLSTATE[42000]: Syntax error or access violation: 1055

Assalamualaikum wr. wb.

Hello guys, i will share about how to fix error SQLSTATE[42000]: Syntax error or access violation: 1055 in laravel 5.6

I get this error when i create script for retrieving data from 2 table with join. Below is my code.

$userprodi = DB::table('users')
        ->join('prodis', 'users.id_prodi', '=', 'prodis.id')
        ->select( 'prodis.nama_prodi',DB::raw("count(prodis.id) as jumlah"))
        ->groupBy('users.id_prodi')
        ->get();

And then i get this errors

I think my query is not wrong, and i execute this query to phpmyadmin and get success no error.

Why i get error ?

This is maybe a SQL_MODE problem, and you can fix this error (SQLSTATE[42000]: Syntax error or access violation: 1055).

1. Open config/database.php
2. Change strict value from true to false.

        'mysql' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'strict' => false, //change this value
            'engine' => null,
        ],

3. Save

Finish, your error is fixed.

Subscribe to receive free email updates: