Laravel : Syntax error or access violation: 1055 Error

Error message¶

public function uploads($count)
    {
        $items = \DB::table('article')
            ->select('pcode.name', \DB::raw('count(*) as upload_count'))
            ->leftJoin('pcode', 'article.organization_id', '=', 'pcode.id')
            ->where([
                ['pcode.type', '=', "organization_type"],
                ['article.status', '=', 1]
            ])
            ->groupBy('organization_id');
        return $items->paginate($count);
    }

The above piece of code, the test server is very good, and an error was reported after going online.

Syntax error or access violation: 1055 Error: MySQL: isn't in GROUP BY
etc.

solution and reason¶

Later, after googling for a while, I learnt that the problem was caused by the sql_mode setting, and modified the configuration of config/database.php as strict => false.

'mysql' => [
    'driver' => 'mysql',
    'host' => env('DB_HOST', 'localhost'),
    'port' => env('DB_PORT', '3306'),
    'database' => env('DB_DATABASE', 'forge'),
    'username' => env('DB_USERNAME', 'forge'),
    'password' => env('DB_PASSWORD', ''),
    'charset' => 'utf8',
    'collation' => 'utf8_unicode_ci',
    'prefix' => '',
    'strict' => false,
    'engine' => null,
],

Then I tried it and the problem was solved, and then I checked the reason carefully:

Query the mysql 1055 error code and find that the problem is that if sql_mode is set in the mysql configuration,
ONLY_FULL_GROUP_BY is worth talking about, you need to include all the select fields in the group by when making a query.
I.e. select x,y from xxx group by x,y
Otherwise it will report an error

summary¶

Laravel 5.3 turns on mysql strict mode by default.
When mysql is in strict mode and ONLY_FULL_GROUP_BY is turned on,
If the group by field does not appear in the select statement, an error will be reported. If the strict mode is turned off, no error will be reported.