Spread the love

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

iduser_idgroup_idmessageis_readupdated_atcreated_at
111hi12022-09-07 22:00:002022-09-07 22:00:00
211hi sec02022-09-07 22:02:002022-09-07 22:02:00
321hi last12022-09-07 23:00:002022-09-07 23:00:00
422hi g202022-09-07 22:00:002022-09-07 22:00:00
532hi g2 sec12022-09-07 22:02:002022-09-07 22:02:00
632hi g2 last12022-09-07 23:00:002022-09-07 23:00:00
group_messages

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

iduser_idgroup_idmessageis_readupdated_atcreated_at
111hi12022-09-07 22:00:002022-09-07 22:00:00
422hi02022-09-07 22:00:002022-09-07 22:00:00
group_messages with group by

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"), .

Leave a Reply