Laravel Eloquent 和 SQL：对具有最大日期的行进行分组和选择-解网

问：

我遇到了使用 Eloquent 根据 Laravel 中的最大日期分组和选择行的问题。我有一个名为 AlertsLog 的表，其中包含列 id、price 和 created_at。目标是按价格对行进行分组，并为每个组选择具有最新created_at的条目。我尝试了几种方法，包括 groupBy、max 和 subquery，但我仍然缺少一些预期的结果。

这是我的 SQL 虚拟数据的样子

[
  {"id": 1, "price": 1010, "created_at": "2023-11-09 01:22 PM"},
  {"id": 2, "price": 1010, "created_at": "2023-11-09 01:35 PM"},//the price 
  {"id": 3, "price": 1007, "created_at": "2023-11-09 01:40 PM"},//the price has changed
  {"id": 4, "price": 1007, "created_at": "2023-11-09 01:45 PM"},
  {"id": 5, "price": 1010, "created_at": "2023-11-09 01:50 PM"},//the price has changed
  {"id": 6, "price": 1015, "created_at": "2023-11-09 01:55 PM"},//the price has changed
  {"id": 7, "price": 1015, "created_at": "2023-11-09 02:00 PM"} //the latest price
]

我想展示的是

[
 {"id":2,"price":1010,"created_at":"2023-11-09 01:35 PM"},
 {"id":4,"price":1007,"created_at":"2023-11-09 01:45 PM"},
 {"id":5,"price":1010,"created_at":"2023-11-09 01:50 PM"},
 {"id":7,"price":1015,"created_at":"2023-11-09 02:00 PM"}
]

到目前为止，我使用group by尝试过：

$uniqueAlerts = DB::table('alerts_logs')
        ->whereIn('id', function ($query) {
            $query->select(DB::raw('MAX(id)'))
                ->from('alerts_logs')
                ->groupBy('price');
        })
        ->orderBy('created_at', 'desc')
        ->get();

使用 Unique：

$alerts = AlertsLog::orderBy('created_at', 'desc')->get();

return $uniqueAlerts = $alerts->unique('price')->sort(function ($a, $b) {
    return $b->created_at <=> $a->created_at;
})->values()->toArray();

甚至使用循环，但它们都产生相同的结果，包括 ID 4,5,7 但缺少 2 ，

谢谢！

mysql laravel eloquent laravel-query-builder

AlertsLog::from('AlertsLog as a')
        ->leftJoin('AlertsLog as b', function ($join) {
            $join->on('a.id', '=', DB::raw('(b.id + 1)'));
        })
        ->whereColumn('a.price', '!=', 'b.price')
        //->orWhereNull('b.id')
        ->select('a.*')
        ->orderBy('a.id')
        ->get();

MySQL查询是：

select `a`.*
from `AlertsLog` as `a`
         left join `AlertsLog` as `b` on `a`.`id` = (b.id + 1)
where `a`.`price` != `b`.`price`
order by `a`.`id` asc

上一个：无法聚合包含相关子查询的查询的结果而不会超时 - mysql 5.7

下一个：MySQL join 返回 null

Laravel Eloquent 和 SQL：对具有最大日期的行进行分组和选择

Laravel Eloquent & SQL: Grouping and Selecting Rows with Maximum Date

评论