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.

CodePudding user response：

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