Today, I was working on a project which is being developed on Laravel 8 and I created a MySQL query with joins and GROUP BY clause and it was executing correctly if, I was running directly on database however, when i place the same query in Laravel DB::select() method, it was giving ‘syntax error or access violation 1055 in group by’ error in error log. Here is the code that I was running in laravel 8.
DB::select("SELECT * FROM (SELECT m.*, COUNT(mh.id) AS total_days FROM members m LEFT JOIN member_history mh ON (mh.`member_id` = m.`id`) WHERE m.`member_status` = 'active' GROUP BY mh.`member_id` ) AS member WHERE member.total_days > 6");
And then, I searched for the solution over the Internet and came to know that, I have to make the ‘strict’ mode to false in config/database.php. previously it was true.
'mysql' => [ ....... 'strict' => false, //before it was true ....... ],