Issue with Laravel Eloquent query, but the built query works when executed manually

Issue

I am working on a Laravel API where I am using Eloquent to access a MySQL database with a couple of joins and a group by clause.

When Eloquent runs I get an error, but the error outputs the built SQL which when I run manually on the database, it works fine, so I’m a little confused what the problem might be.

The Eloquent query builder is as follows:

$issues = \DB::table('crash_groups')
                ->join('projects', 'projects.project_id', '=', 'crash_groups.project_id')
                ->join('crash_info', 'crash_info.crash_group_id', '=', 'crash_groups.crash_group_id')
                ->where('projects.organisation_id', $organisation_id)
                ->where('crash_info.created_at', '>', $time_interval_sql)
                ->groupBy('crash_info.crash_group_id')
                ->get();

When the above is executed I get the following error outputted

SQLSTATE[42000]: Syntax error or access violation: 1055
‘crash_groups.crash_group_id’ isn’t in GROUP BY
(SQL: select * from crash_groups inner join projects on
projects.project_id = crash_groups.project_id inner join
crash_info on crash_info.crash_group_id =
crash_groups.crash_group_id where projects.organisation_id = 1
and crash_info.created_at > NOW() – INTERVAL 1 DAY group by
crash_info.crash_group_id)

If I manually take the SQL query that is within the error log and run that directly in the database, I then get 2 rows back as I am expecting so I don’t understand why I’m getting an SQL error about the Group By when Eloquent executes when the query that it actually builds and logs as being an issue actually works.

Solution

Correct answer for this question is to either include the columns in a select like

$issues = \DB::table('crash_groups')
                ->select('crash_info.crash_group_id')
                ->join('projects', 'projects.project_id', '=', 'crash_groups.project_id')
                ->join('crash_info', 'crash_info.crash_group_id', '=', 'crash_groups.crash_group_id')
                ->where('projects.organisation_id', $organisation_id)
                ->where('crash_info.created_at', '>', $time_interval_sql)
                ->groupBy('crash_info.crash_group_id')
                ->get();

Another way is to disable mysql strict mode.
To disable strict mode, edit in in config/database.php.

'mysql' => [ 
'strict' => false, //'strict' => true, 
], 

Answered By – Patrik Grinsvall

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0