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.
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 thegroup by
when making a query.
I.e.select x,y from xxx group by x,y
Otherwise it will report an error
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.