How can I solve incompatible with sql_mode=only_full_group_by in laravel eloquent?
My laravel eloquent is like this :
$products = Product::where('status', 1)
->where('stock', '>', 0)
->where('category_id', '=', $category_id)
->groupBy('store_id')
->orderBy('updated_at', 'desc')
->take(4)
->get();
When executed, there exist error like this :
SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'myshop.products.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (SQL: select * from
products
wherestatus
= 1 andstock
> 0 andcategory_id
= 5 group bystore_id
order byupdated_at
desc limit 4)
How can I solve it?
I had a similar Problem and solved it by disabling mysql strict mode in the database connection setting.
'connections' => [
'mysql' => [
// Behave like MySQL 5.6
'strict' => false,
// Behave like MySQL 5.7
'strict' => true,
]
]
You can find even more configuration settings in this blog post by Matt Stauffer
In folder config => database.php make sure mysql strict is false, like this
'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' => 'utf8',
'collation' => 'utf8_general_ci',
'prefix' => '',
'strict' => false,
'engine' => null,
],
if strict is true, make it false then clear config cash by run this command in cmd
php artisan config:clear
I solved this problem by adding the "modes" option and setting only the modes I want to be enabled in config => database.php
'mysql' => [
...
'modes' => [
'STRICT_ALL_TABLES',
'ERROR_FOR_DIVISION_BY_ZERO',
'NO_ZERO_DATE',
'NO_ZERO_IN_DATE',
'NO_AUTO_CREATE_USER',
],
],
See more details in this tutorial