Order By before Group By using Eloquent (Laravel)
I just needed to do something similar with a messages model. What worked for me was applying the unique
method on the returned eloquent collection.
Model::where('toId', $id)
->orderBy('createdAt', 'desc')
->get()
->unique('fromId');
The query will return all messages ordered by createdAt
and the unique
method will reduce it down to one message for each fromId
. This is obviously not as performant as using the database directly, but in my case I have further restrictions on the query.
Also, there are many more useful methods for working with these collections: https://laravel.com/docs/5.2/collections#available-methods
I found a way to do that! Basically, creating a subquery and running it before, so that results are ordered as expected and grouped after.
Here is the code:
$sub = Message::orderBy('createdAt','DESC');
$chats = DB::table(DB::raw("({$sub->toSql()}) as sub"))
->where('toId',$id)
->groupBy('fromId')
->get();