Laravel Eloquent的最新记录 [英] Laravel Eloquent group by most recent record

问题描述

我正在尝试获取桌子上单个客户的最新记录.示例:

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

请注意，该表还具有created_at和Updated_at字段.为了简单起见，我省略了这些字段.

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

最后，我希望查询返回Cust001:

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

对于Cust002:

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

我尝试过:

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

还有

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

我该如何实现?如果对您来说更简单，您可以在此处编写SQL查询，我将找到一种将其翻译"为Laravel语法的方法.

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.

推荐答案

要获取基于created_at的每个城市的每个客户的最新记录，可以使用自动加入

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();

在普通SQL中，它将类似于

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

演示

具有内部内连接的另一种方法是

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

演示

这篇关于Laravel Eloquent的最新记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！