Laravel – Eloquent relationship belongsToMany groupBy “Syntax error or access violation” Error

Solution:

the problem was with the strict mode in config/database.php.

There are only 2 solutions so far:

1. Disable strict mode; or
2. Include all columns selected in the belongsToMany tables in groupBy or in aggregate functions such as SUM() etc.

This is an example in 2:

    return $this->belongsToMany('App\Models\Team', 'team_members', 'user_id', 'team_id')
        ->selectRaw('sum(team_members.team_member_role_id) as pivot_count') //because this is different across records beside others in group by
        ->groupBy('teams.id', 'teams.name', 'teams.description', 'team_members.user_id', 'team_members.team_id');

Please notice that you can choose to SUM() columns in records that are different but can be grouped by the rest of the columns and you don’t care about that specific column. For example:

job         |   gender
programmer  |   M
programmer  |   F
accountant  |   M

If you don’t want to distinguish between jobs with different genders, then SUM() the gender, so you don’t have to add that column after GROUP BY.