Skip to content
Readerstacks logo Readerstacks
  • Home
  • Softwares
  • Angular
  • Php
  • Laravel
  • Flutter
Readerstacks logo
Readerstacks
How to Get Last Record With Group By in Laravel Eloquent

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

Aman Jain, September 7, 2022September 7, 2022

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

Share this:

  • Facebook
  • X

Related

Php Laravel Laravel 9 Eloquentgroupbylaravellast

Post navigation

Previous post
Next post

Related Posts

Create Custom Class or Library Class in Laravel 10

March 14, 2023November 8, 2023

In Laravel, you can create custom classes or library classes that can be used throughout your application. These classes can be used to encapsulate common functionality, business logic, or to abstract away complex operations. Thus I this article i will show you to create a class and use it using…

Share this:

  • Facebook
  • X
Read More
Laravel How to Find the laravel version and location

How to find installed laravel version?

March 5, 2022March 5, 2022

To find the installed laravel version in command line we can use artisan command and to find the laravel version in application code we can use app method. In this article i will show you to get the version of laravel version in both ways using command line and in…

Share this:

  • Facebook
  • X
Read More

Show dropdown list from ajax response in jQuery

September 10, 2021September 10, 2021

Showing Dynamic Select Box using jQuery and Php,MySql In this tutorial, we are going to learn the dropdown list using AJAX. Loading dropdown values from AJAX requires knowledge of javascript, jQuery, php and html. Sometimes it requires to load the dropdown like countries, state, city list dependently. In this case…

Share this:

  • Facebook
  • X
Read More

Leave a ReplyCancel reply

Aman Jain
Aman Jain

With years of hands-on experience in the realm of web and mobile development, they have honed their skills in various technologies, including Laravel, PHP CodeIgniter, mobile app development, web app development, Flutter, React, JavaScript, Angular, Devops and so much more. Their proficiency extends to building robust REST APIs, AWS Code scaling, and optimization, ensuring that your applications run seamlessly on the cloud.

Categories

  • Angular
  • CSS
  • Dart
  • Devops
  • Flutter
  • HTML
  • Javascript
  • jQuery
  • Laravel
  • Laravel 10
  • Laravel 9
  • Mysql
  • Php
  • Softwares
  • Ubuntu
  • Uncategorized

Archives

  • December 2023
  • November 2023
  • October 2023
  • July 2023
  • March 2023
  • November 2022
  • October 2022
  • September 2022
  • August 2022
  • July 2022
  • June 2022
  • May 2022
  • April 2022
  • March 2022
  • February 2022
  • January 2022
  • December 2021
  • November 2021
  • October 2021
  • September 2021
  • August 2021
  • July 2021
  • June 2021

Recent Posts

  • How to Call Controller Method from Another Controller in Laravel ?
  • How to Get Domain Name in Laravel ?
  • How to Append Query String to Route in Laravel ?
  • How to Append URL Query Params to Pagination Laravel ?
  • How to Get Today Records in Laravel ?
©2023 Readerstacks | Design and Developed by Readerstacks
Go to mobile version