Laravel Eloquent group by most recent record

Question

I'm trying to get the most recent record for a single customer on a table. Example:

ID    Customer    City    Amount
1     Cust001     City1   2
2     Cust001     City2   3
3     Cust001     City1   1
4     Cust001     City2   1
5     Cust001     City2   3
6     Cust001     City3   1
7     Cust001     City3   1
8     Cust002     City1   2
9     Cust002     City1   1
10    Cust002     City2   3
11    Cust002     City1   2
12    Cust002     City2   1
13    Cust002     City3   2
14    Cust002     City3   3
15    Cust003     City1   1
16    Cust003     City2   3
17    Cust003     City3   2

Please note that the table also has created_at and updated_at fields. I omitted those fields for simplicity.

In the end I want my query to return for Cust001:

ID    Customer    City    Amount
3     Cust001     City1   1
5     Cust001     City2   3
7     Cust001     City3   1

And for Cust002:

ID    Customer    City    Amount
11    Cust002     City1   2
12    Cust002     City2   1
14    Cust002     City3   3

I've tried:

Table::where('Customer', 'Cust001')
    ->latest()
    ->groupBy('City')
    ->get()

and also

Table::select(DB::raw('t.*'))->from(DB::raw('(select * from table where Customer = \'Cust001\' order by created_at DESC) t'))
    ->groupBy('t.City')->get();

But it keeps returning the oldest record on each group (and I want the most recent).

How can I achieve this? If is easier for you guys, you can write the SQL Query here and I will find a way to "translate it" to Laravel syntax.

id or created_at. I just need one and I'll tweak it if needed. Thanks — TJ is too short, Dec 15 '17 at 10:23

M Khalid Junaid · Answer 1 · 2017-12-15T10:33:39.937

1

To get latest record per customer among for each city based on created_at you can use a self join

DB::table('yourTable as t')
  ->select('t.*')
  ->leftJoin('yourTable as t1', function ($join) {
        $join->on('t.Customer','=','t1.Customer')
             ->where('t.City', '=', 't1.City')
             ->whereRaw(DB::raw('t.created_at < t1.created_at'));
   })
  ->whereNull('t1.id')
  ->get();

In plain SQL it would be something like

select t.*
from yourTable t
left join yourTable t1
on t.Customer = t1.Customer
and t.City = t1.City
and t.created_at < t1.created_at
where t1.id is null

Demo

Another approach with self inner join would be

select t.*
from yourTable t
join (
    select  Customer,City,max(ID) ID
    from yourTable
    group by Customer,City
) t1
on t.Customer = t1.Customer
and t.City = t1.City
and t.ID = t1.ID

Demo

edited Dec 15 '17 at 10:33

answered Dec 15 '17 at 10:24

M Khalid Junaid

63,861
10
90
118

Thanks very much! How can I adapt the query to return the records for only one customer? Let's say that I want to see only the records related to Cust001 (like in the example) – TJ is too short Dec 15 '17 at 10:42
@TJistooshort you can add `->where('t.customer','=','Cust001')` in outer query – M Khalid Junaid Dec 15 '17 at 10:46
Unfortunately the query takes ages to load. The table contains almost 1 million records and is taking too load to return the results. Is there any way to optimise this? – TJ is too short Dec 15 '17 at 11:20
A composite index on customer,city & created_at will help i guess – M Khalid Junaid Dec 15 '17 at 11:21

Laravel Eloquent group by most recent record

1 Answers1

Linked