Level 75
If you turn off strict mode for this one query only, does it work or do you get an error.
3 likes
Sep 18, 2021
4
Level 1
How to write the eloquent query without using DB::raw so that it doesn't have to put MySQL strict mode to false.
How to write the eloquent query to select the max value without using DB::raw and writing in pure eloquent so that it doesn't have to put MySQL strict mode to false. Means any alternative to write, DB::raw('max(price) as price') without making MySQL strict mode to false.
Item::join('product', 'item.id', '=', 'product.item_id')
->select(['item_id', 'item.title',DB::raw('max(price) as price')])
->where('product.user_id', '=', $user->id)
->groupBy('product.item_id')
->get();
Level 1
@jlrdw If I make strict mode to false then I don't get an error. However, if the MySQL strict mode is turn on then I do get an error. I actually don't want to turn MySQL strict mode to false to run this code and so is there any way to get the same. Actually, DB::raw('max(price) as price') is the one that is throwing the error as it is a raw query. Sir can you please help me to achieve it.
Level 75
See the manual and read about strict modes.
https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html
And you can turn off strict mode for one query only if needed. It isn't a security problem.
https://laracasts.com/discuss/channels/guides/turn-off-only-full-group-by-for-just-one-query-guide
The only other solution is rewrite your query to avoid the error. Also using raw on an aggregate is pretty standard.
Make sure to select all fields used in query.
4 likes
Level 1
@jlrdw Sir thank you. Your guide really helped me in making MySQL strict mode false to only one query when needed.
config()->set('database.connections.mysql.strict', false);
\DB::reconnect(); //important
Model::select()->get();
//now changing back the strict ON
config()->set('database.connections.mysql.strict', true);
\DB::reconnect();
2 likes
Please or to participate in this conversation.