Using ‘groupBy’ method in Laravel

Debiprasad

·

Follow

Published in

DEVi

·

·

Jan 10, 2020

2 min read

—

I often learn from my mistakes. And I prefer to learn by doing things. Today, I tried to group the rows of the result by a value of one of the columns. Then it threw an exception.

Illuminate\Database\QueryException SQLSTATE[42000]: Syntax error or access violation: 1055 ‘***.resources.id’ isn’t in GROUP BY (SQL: select * from `resources` where `lesson_number` <= 12 group by `lesson_number` order by `lesson_number` asc, `title` asc)

The following was my code.

$groupedResources = Resource::orderBy('lesson_number')
                            ->orderBy('title')
                            ->groupBy('lesson_number')
                            ->get();

When I tried to find a solution for this, I came to know that MySQL strict mode (which includes ONLY_FULL_GROUP_BY mode) does not allow you to select columns which are not in the GROUP BY. Most answers to a question on this on StackOverflow suggest to disable strict mode or ONLY_FULL_GROUP_BY mode. I understood that I have done something wrong, but disabling MySQL strict mode is not the solution. I tried to enter the columns I need in the select query. Still, I got a similar error for another column. I turned off MySQL strict mode for some time to see the result and then I understood my mistake.

This restriction makes sense as when you use GROUP BY in MySQL, it returns one row for each value in the columns used in GROUP BY. So, the values of other columns in the selected rows do not make sense to use anywhere. So, it’s always recommended to use the best practice and I would recommend not to disable MySQL strict mode.

Often developers, like me, may need rows of a query grouped by the value of a column. Here they don’t need only one row per the unique values of the columns. But they need multiple rows grouped by the unique values of a particular column. For some reason, they use groupBy Query Builder method of Laravel which generates a MySQL GROUP BY query and the developers encounter the above error.

The solution to their problem is to use groupBy Collection method instead.

$groupedResources = Resource::orderBy('lesson_number')
                            ->orderBy('title')
                            ->get()
                            ->groupBy('lesson_number');

Did you see the difference? groupBy method before get method acts as a Query Builder method while after get method acts as a Collection method. This will give them the desired result.