Laravel Eloquent 选择最大 created_at 的所有行 [英] Laravel Eloquent select all rows with max created_at

问题描述

我有一张包含以下内容的表格:

I have a table that contains:

id  seller_id   amount   created_at
1   10          100      2017-06-01 00:00:00
2   15          250      2017-06-01 00:00:00
....
154 10          10000    2017-12-24 00:00:00
255 15          25000    2017-12-24 00:00:00

我想获取每个单独的 Seller_id 的所有最新行.我可以像这样获得最新的一行:

I want to get all the latest rows for each individual seller_id. I can get the latest row for one like this:

$sales = Snapshot::where('seller_id', '=', 15)
    ->orderBy('created_at', 'DESC')
    ->first();

如何只获取每个卖家的最新行?

How do I get only the latest row for each seller?

推荐答案

要获取每个seller_id的最新记录，您可以使用以下查询

To get latest record for each seller_id you can use following query

select s.*
from snapshot s
left join snapshot s1 on s.seller_id = s1.seller_id
and s.created_at < s1.created_at
where s1.seller_id is null

使用查询构建器，您可以将其重写为

Using query builder you might rewrite it as

DB::table('snapshot as s')
  ->select('s.*')
  ->leftJoin('snapshot as s1', function ($join) {
        $join->on('s.seller_id', '=', 's1.seller_id')
             ->whereRaw(DB::raw('s.created_at < s1.created_at'));
   })
  ->whereNull('s1.seller_id')
  ->get();

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