How can I solve incompatible with sql_mode=only_full_group_by in laravel eloquent?

Question

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 where status = 1 and stock > 0 and category_id = 5 group by store_id order by updated_at desc limit 4)

How can I solve it?

Answer 1

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