How to get first record of each group created using groupBy() with Laravel eloquent model?

Question

I have a table structured as following named renewals.

------------------------------------------------
| id  | membership_id| valid_from | valid_upto |
------------------------------------------------
|  1  | 1            | 2015-02-15 | 2016-02-15 |
|  2  | 2            | 2015-05-16 | 2016-05-16 |
|  3  | 2            | 2016-05-16 | 2017-05-16 |
|  4  | 3            | 2014-06-16 | 2015-06-16 |
|  5  | 3            | 2015-06-16 | 2016-06-16 |
|  6  | 3            | 2016-06-16 | 2017-06-16 |
|  7  | 1            | 2016-02-15 | 2017-02-15 |
------------------------------------------------

I need to get record of each members with latest valid_upto date. How to do that using Laravel Eloquent. Any other easy method will be appreciated.

score 0 · Answer 1 · answered Oct 12 '17 at 10:28

In User Model

public function latest_renewal()
{
    return $this->hasOne('App\Renewals', 'membership_id')->latest('valid_upto');
}

In controller

$users = User::with('latest_renewal')->get();
/*
 [
   {
     'id' => 1,
     'name' => 'user1',
     'latest_renewal' => {
         'id' => 7,
         'membership_id' => 1,
         'valid_from' => '2016-02-15',
         'valid_upto' => '2017-02-15',

     }
   },
   {
     'id' => 2,
     'name' => 'user2',
     'latest_renewal' => {
         'id' => 3,
         'membership_id' => 2,
         'valid_from' => '2016-05-16',
         'valid_upto' => '2017-05-16',

      }
    }
 ]

score 0 · Answer 2 · 2017-10-12T16:18:36.073

0

Renewals::groupBy('membership_id')->havingRaw('MAX(valid_upto)')->get();

or

Renewals::groupBy('membership_id')->having( function( $query )
{
    $query->latest('valid_upto');
})->get();

edited Oct 12 '17 at 16:18

answered Oct 12 '17 at 16:04

score 0 · Answer 3 · edited Jan 19 '18 at 11:04

In plain MySQL, you could get your latest records per member by following:

select a.*
from renewals a
left join renewals b on a.membership_id = b.membership_id
and a.valid_upto < b.valid_upto
where b.membership_id is null

DEMO

Using Laravel, you could write it using query builder as:

DB::table('renewals as a')
  ->select('a.*')
  ->leftJoin('renewals as b', function ($join) {
        $join->on('a.membership_id','=','b.membership_id')
             ->whereRaw(DB::raw('a.valid_upto < b.valid_upto'));
   })
  ->whereNull('b.membership_id')
  ->get()

score 0 · Answer 4 · answered Sep 29 '21 at 09:32

0

May be something simlilar to this

DB::statement("SET SQL_MODE=''");

Renewal::groupBy('membership_id')
         ->latest('valid_upto')
         ->get();

Read more about the first SET SQL_MODE: Laravel : Syntax error or access violation: 1055 Error

answered Sep 29 '21 at 09:32

hungtran273

1,180
9
11

Nitsh · Answer 5 · 2022-07-02T01:32:20.760

0

$data = Renewal::get();
$groups = $data->groupBy('membership_id');
$results = [];
foreach($groups as $group) {
   /*collection*/
   $results[] = $group[0];
}
return $results;

source : https://stackoverflow.com/a/29731825/13381751

edited Jul 02 '22 at 01:32

answered Jul 02 '22 at 01:17

Nitsh

79
3

score -1 · Answer 6 · edited Jan 19 '18 at 11:03

-1

You can use below query for your purpose.

$renewals = Renewals::groupBy('membership_id')->orderBy('valid_upto','desc')->get();

edited Jan 19 '18 at 11:03

halfer

19,824
17
99
186

answered Oct 12 '17 at 10:30

AddWeb Solution Pvt Ltd

21,025
5
26
57

This gets all records, which is not what OP asked for. – Don't Panic Oct 12 '17 at 14:46

How to get first record of each group created using groupBy() with Laravel eloquent model?

6 Answers6