Laravel 8: Solved error ‘syntax error or access violation 1055 in group by’

By | May 22, 2021

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
            .......
        ],