unable to GROUP_CONCAT for 'one to many relationship' when query has multiple other joins

Question

I am unable to use GROUP_CONCAT for 'one to many relationship' when the query has multiple other joins.

This is the error message:

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'laravel.profile.id' which is not functionally dependent on columns in GROUP BY clause; this is

The query:

return DB::table('users as u')
            ->select('u.id','profile.*',DB::raw("group_concat(photos.url_link) as user_images"))
            ->leftjoin('user_photos as photos', 'u.id', '=', 'photos.id')
            ->leftjoin('user_profiles as profile', 'u.id', '=', 'profile.user_id')
            /*/* ->groupBy('photos.user_id')*/
            ->groupBy('u.id','profile.user_id')
            ->where('u.id', $id)
            ->first();

Table relationships:

User is in 'one to many' relationship with photos

User is in 'one to one' relationship with profile

the query works if i take out the groupBy and the 'group_concat' part of the query:

return DB::table('users as u')
            ->select('u.id as user_id','profile.*')
            ->leftjoin('user_photos as photos', 'u.id', '=', 'photos.id')
            ->leftjoin('user_profiles as profile', 'u.id', '=', 'profile.user_id')
            ->where('u.id', $id)
            ->first();

The query also works if I only have one join:

this query works:

return DB::table('users as u')
            ->select('u.id',DB::raw("group_concat(photos.url_link) as user_images"))
            ->leftjoin('user_photos as photos', 'u.id', '=', 'photos.id')
            ->groupBy('u.id')
            ->where('u.id', $id)
            ->first();

[Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregate](https://stackoverflow.com/search?q=sELECT+list+is+not+in+GROUP+BY+clause+and+contains+nonaggregate) error is quite common in SO — Ergest Basha, Aug 22 '23 at 09:58
Does this answer your question? [Laravel : Syntax error or access violation: 1055 Error](https://stackoverflow.com/questions/40917189/laravel-syntax-error-or-access-violation-1055-error) — miken32, Aug 24 '23 at 18:33

Jon White · Accepted Answer · 2023-08-24T05:18:17.353

Your issue is in the select columns where you're selecting profile.*

profile.* is adding other columns to the select that aren't in your grouped columns or aggregated like your GROUP_CONCAT

If you only select the profile.user_id column from the joined profile table your query should work.

return DB::table('users as u')
            ->select('u.id','profile.user_id',DB::raw("group_concat(photos.url_link) as user_images"))
            ->leftjoin('user_photos as photos', 'u.id', '=', 'photos.id')
            ->leftjoin('user_profiles as profile', 'u.id', '=', 'profile.user_id')
            /*/* ->groupBy('photos.user_id')*/
            ->groupBy('u.id','profile.user_id')
            ->where('u.id', $id)
            ->first();

It may help to think of GROUP BY more as an aggregation than actually grouping.

With Laravel's collections, for example, the group method takes records that share the value you're grouping by and organises the collection into a series of sub-collections. It only cares about the data it's grouping by.

With SQL it's more a case of merging a number of records into a single row, it needs to know what to do with the ungrouped values. Let's say you have a table of addresses:

city	street	number
London	Oxford Street	100
London	Oxford Street	101
London	Carnaby Street	200
London	Carnaby Street	201
Bristol	Corn Street	302

If I group by City, SQL wants to give me back two records (London and Bristol) so I need to either aggregate the remaining columns with something like GROUP_CONCAT() or MAX() or further divide the results by adding them to the group parameters.

If I group by City and Street then it wants to give me 3 records (London/Oxford Street, London/Carnaby Street and Bristol/Corn Street) so I only need to aggregate the number column.

unable to GROUP_CONCAT for 'one to many relationship' when query has multiple other joins

1 Answers1