Laravel's groupBy() not working

Try with query builder

$logs = DB::table('callLog')
                ->groupBy('caller')
                ->get();

https://laravel.com/docs/5.3/queries#ordering-grouping-limit-and-offset

In MySQL 5.7, the sql mode ONLY_FULL_GROUP_BY is enabled by default, which means your select list must be in your aggregate functions (GROUP_BY, HAVING) assuming they are functionally dependent. See (seriously): https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

This will work:

CallLogModel::selectRaw('count(*) AS cnt, caller')->groupBy('caller')->orderBy('cnt', 'DESC')->limit(5)->get();

This will not:

CallLogModel::selectRaw('count(*) AS cnt, caller, callee')->groupBy('caller')->orderBy('cnt', 'DESC')->limit(5)->get();

If caller isn't functionally dependent on caller (and it wouldn't be), you can do this:

CallLogModel::selectRaw('count(*) AS cnt, caller, ANY_VALUE(callee)')->groupBy('caller')->orderBy('cnt', 'DESC')->limit(5)->get();

Or, again, disable ONLY_FULL_GROUP_BY. There is some other stuff out there if you want to read further about the pros/cons of leaving ONLY_FULL_GROUP_BY enabled. For most cases, it's probably fine to disable. Just keep it in the back of your mind as you write are verify your query results.

So here's what I did:

        $calls = Calls::select('call_id', DB::raw('COUNT(call_id) as count'))
            ->with('called')
            ->groupBy('call_id')
            ->orderBy('count', 'desc')
            ->get();

And this works, but it only obviously pulls in call_id and I can get data from called relationship, BUT I have another relationship called "info" and I need to pull info_id as well to get it with with('info')... So when I try to add that field to select it returns that same error from the original comment... Any thoughts @tomi ?

EDIT:

I just saw @lindstrom post and it freaking worked, I used ANY_VALUE(info_id) as info_id and got the relation "with('info')" working!!! Awesome answer!

@lindstrom Yeah, but I don't want to touch those settings on EBS... My manager always says "they're there for a reason". :)

@dlook The only way that response from your manager flies is if he's non-IT (in that case you're screwed). Otherwise it's a complete bullshit cop out. If he's being that dismissive, he sure as hell better be able to either a) enumerate the reasons from either knowledge/experience or research or b) pay someone to make these calls. He should know, for example, that if you migrated from 5.6 to 5.7, the only sql mode enabled by default was NO_ENGINE_SUBSTITUTION. Prior to 5.6.6, no modes were enabled. And from the fine manual for 5.7:

"The default SQL mode in MySQL 5.7 includes these modes: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, and NO_ENGINE_SUBSTITUTION.

The ONLY_FULL_GROUP_BY and STRICT_TRANS_TABLES modes were added in MySQL 5.7.5. The NO_AUTO_CREATE_USER mode was added in MySQL 5.7.7. The ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and NO_ZERO_IN_DATE modes were added in MySQL 5.7.8. For information about all available modes and default MySQL behavior, see Section 6.1.8, “Server SQL Modes”."

At any rate, I'm still happy you got it working.

My manager always says "they're there for a reason".

Sounds like manager speak for "I dunno wtf it's for, so I don't want you to touch it either." :D

@dlook what are you building? :D

Go to your config/database.php folder. In mysql configuration array, change strict => true to strict => false, and everything will work nicely.

A note on @cent040's - you can control the modes individually if you wanted/needed to. See the end of this blog post from @mattstauffer here: https://mattstauffer.com/blog/strict-mode-and-other-mysql-customizations-in-laravel-5-2/ (and kudos for @adamwathan for the PR! :)

old issue i know, but this could help someone. :)

i got the same error and i just moved the groupBy to work on the collection returned from the query and not the query itself.

if you work with large datasets i would go for a groupBy in the database, but for something small groupBy on the collection should be fine.

EDIT: just realized you probably are trying to accomplish something else.

@lindstrom Maybe you can help me. Look at this : https://laracasts.com/discuss/channels/laravel/why-having-only-worked-on-fields-that-are-group-by-on-laravel-eloquent

@COTIGA - thank you, problem solved

In the case you don't want to change the database settings, run the query without groupBy, and then apply the groupBy on the result collection.

in my case simplePaginate() works with groupBy()

Thanks man, that worked for me in Laravel 5.8!

You are the saver! Thanks a lot!

Best explanation I found on internet ever! Thanks a lot!

Thanks a lot man, i was looking for this, and you just got me a solution that works for me <3

@cotiga this working perfectly! but, can you explain a little bit about the function? thanks

@cotiga hi. I'm using 'sqlsrv'. Can you please tell me how to apply it in sqlsrv configuration? Thank you.