Group By using Eloquent (Laravel)

Question

enter image description hereThe Problem occur when I add group by in my query...

Form::select('*')->groupBy('serial_num')->orderBy('id')->Paginate(10);

SQLSTATE[42000]: Syntax error or access violation: 1055 'database.form.id' isn't in GROUP BY (SQL: select * from form group by serial_num limit 10 offset 0)

What are you trying to accomplish with this query? Can you show us sample input and output data? The query you wrote does not make sense, but then again perhaps it is not what you are intending to do. — Tim Biegeleisen, Jun 24 '19 at 06:08
Yes I have a Model name. I want to list all records but i want to group by those records which have same serial number... — Hedayatullah Sarwary, Jun 24 '19 at 06:17

Michel Feldheim · Answer 1 · 2019-06-24T07:00:08.683

This is not related to Laravel. You‘re selecting non-aggregated columns (*) in combination with grouping.

You have two options here

use aggregation (Min, Max, Avg, Group_concat) on each column you‘re selecting and not grouping by
disable ONLY_FULL_GROUP_BY on the MySQL server

More detailed explanation https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

Edit: Apparently Laravel can set MySQL modes on the fly as well.

As mentioned in the comments, you can disable strict mode in the Laravel database settings, Laravel then should set the MySQL server mode accordingly, I don’t find up-to-date documentation for this tho

https://github.com/laravel/laravel/blob/master/config/database.php#L59

https://mattstauffer.com/blog/strict-mode-and-other-mysql-customizations-in-laravel-5-2/

make sure you clear any config cache after changing the setting

Shankar S Bavan · Answer 2 · 2019-06-24T06:43:36.217

1

Go to config\database.php

Set

'mysql' => [
    'strict' => false

after you saved, run this command

php artisan optimize

edited Jun 24 '19 at 06:43

answered Jun 24 '19 at 06:30

Shankar S Bavan

922
1
12
32

I changed strict to false but again the same problem occurred, so i changed strict to true – Hedayatullah Sarwary Jun 24 '19 at 06:34
Did you clear cache after you change? – Shankar S Bavan Jun 24 '19 at 06:41
Yes I cleared cache: php artisan config:cache, php artisan cache:clear and also I run php artisan optimize, but it did not worked... – Hedayatullah Sarwary Jun 24 '19 at 06:53
Thanks a lot guys, i cleared the cache and i restarted my server(xampp and laravel) and group by worked... – Hedayatullah Sarwary Jun 24 '19 at 07:54

score 1 · Answer 3 · answered Jun 24 '19 at 07:27

1

If you could handle the Pagination by yourself, then you could obtain the results and then implement groupBy on the collection.

$forms = Form::orderBy('id')->get()->groupBy(function ($item, $key) {
    return $item['serial_num'];
});

answered Jun 24 '19 at 07:27

linktoahref

7,812
3
29
51

Group By using Eloquent (Laravel)

3 Answers3