How to Get Last Record With Group By in Laravel Eloquent ?

In this tutorial we will learn to get last record with group by in laravel eloquent. while working with group by in mysql its gives first record from the group by records that means if you have multiple record with same id and you want the most recent record or last record with same id then mysql will only give you first record not last so to overcome this problem we come here to resolve it in laravel eloquent way.

This example will work in all version of laravel including laravel 5, laravel 6, laravel 7, laravel 8 and laravel 9. we will use whereIn, join and DB::raw methods to get the records.

Suppose we have a group_messages table and here is follow structure and data

so if we group by the group_id column using the eloquent as

GroupMessages::groupBy('group_id')->get();

then we will get the below result

But in real case scenarios we want the last row record of group by rows so here is the solution 1

Solution 1 :

In this solution we will use two queries one to get max id and another to get the records of first eloquent by passing it into second query.

$groupMessages = GroupMessages::select(\DB::raw("max(group_messages.id)"))
                               ->groupBy('group_messages.group_id');

$allMesages    = GroupMessages::whereIn("id",$groupMessages)
                               ->orderBy("id","DESC")
                               ->paginate();                            

So here we only added \DB::raw("group_messages.id") and then used another query for where in .

Solution 2 :

In this solution we will use two queries as well one to get max id and in another we will use inner join with merge bindings.

$groupMessages = GroupMessage::select(\DB::raw("max(group_messages.id) as max_id"))
        ->groupBy('group_messages.group_id');

$allMesages    = GroupMessage::join(\DB::raw("({$groupMessages->toSql()}) as g2"), function ($join) {
                     $join->on("g2.max_id", "=", "group_messages.id");
               })->mergeBindings($groupMessages->getQuery())
                 ->orderBy("id", "DESC")
                 ->get();

So here we only added \DB::raw("group_messages.id") and then used join(\DB::raw("({$groupMessages->toSql()}) as g2"), .