Laravel Eloquent select all rows with max created_at

Question

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

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?

M Khalid Junaid · Accepted Answer · 2017-12-24T08:20:07.067

4

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

edited Dec 24 '17 at 08:20

answered Dec 24 '17 at 08:17

M Khalid Junaid

63,861
10
90
118

@u_mulder thanks first i thought it using alaises as u , u1 – M Khalid Junaid Dec 24 '17 at 08:20
Hi guys, I am using Laravel 5.2 and I get this error from the join: `Call to undefined method Illuminate\Database\Query\JoinClause::whereRaw()`. Is this a version issue do you think? – TheRealPapa Dec 24 '17 at 08:25
@TheRealPapa Can you try `where()` instead if `whereRaw()` i guess this functions in not available in in 5.2 – M Khalid Junaid Dec 24 '17 at 08:29
Ah it was a syntax issue with the answer. Happy holidays and thanks for the help! – TheRealPapa Dec 24 '17 at 08:43

score 1 · Answer 2 · answered Dec 24 '17 at 08:44

This worked:

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

score 0 · Answer 3 · answered Jun 06 '23 at 15:23

Following the answer in https://stackoverflow.com/a/31624248/6779467, in Laravel 10, you can use self::from instead of DB::table to have the same results but with full models instead of just arrays.

in Snapshot model (Snapshot.php)

use Illuminate\Database\Eloquent\Builder;

//...

public function scopeLatestForEachSeller(Builder $query): Builder
{
    /**
     * In case the table name is modified by code elsewhere,
     * we use the table name retrieval method.
     */
    $table = $this->getTable();

    return self::from($table . ' as s')
        ->select('s.*')
        ->leftJoin($table . ' as s1', function ($join) {
            $join->on('s.seller_id', '=', 's1.seller_id')
                ->on('s.created_at', '<', 's1.created_at');
        })
        ->whereNull('s1.seller_id');
}

anywhere else in the code

$snapshots = Snapshot::latestForEachSeller()->get();

Laravel Eloquent select all rows with max created_at

3 Answers3

Linked

Related