Laravel - Eloquent relationship belongsToMany groupBy "Syntax error or access violation" Error

Question

From this question: How to GROUP and SUM a pivot table column in Eloquent relationship?

I used this code in my User Model:

public function teams() {
    return $this->belongsToMany('App\Models\Team', 'team_members', 'user_id', 'team_id')
        ->groupBy('pivot_team_id');
}

I wanted to use ->groupBy() because in my teams, a single user can act as multiple team_members for different roles. I do not want team records duplicate.

But when I try to access e.g. with this code on my page dd(Auth::user()->teams), Laravel threw the following exception:

SQLSTATE[42000]: Syntax error or access violation: 1055 'laravel.teams.id' isn't in GROUP BY (SQL: select `teams`.*, `team_members`.`user_id` as `pivot_user_id`, `team_members`.`team_id` as `pivot_team_id` from `teams` inner join `team_members` on `teams`.`id` = `team_members`.`team_id` where `team_members`.`user_id` = 3 group by `pivot_team_id`)

Then I tried running that exact same SQL in the error by myself, it worked:

Why is that so? And where did I get it wrong?

See if this helps - https://github.com/barryvdh/laravel-translation-manager/issues/144 — ayip, Jun 29 '17 at 19:02
@ayip It does work now, but it seems like a hack. There must be a way to do it without disabling full group — Daniel Cheung, Jun 30 '17 at 04:08

Daniel Cheung · Accepted Answer · 2017-07-20T03:09:30.737

With help of @ayip, the problem was with the strict mode in config/database.php.

There are only 2 solutions so far:

Disable strict mode; or
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.

Laravel - Eloquent relationship belongsToMany groupBy "Syntax error or access violation" Error

1 Answers1