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();