How to Get the Most Recent Row for Each Seller ID Using Laravel Eloquent?

How to Select the Latest Row for Each Seller_ID Using Laravel Eloquent

Problem

You have a table that contains data with seller_id and created_at columns. You want to retrieve only the latest rows for each distinct seller_id.

Solution

Using Laravel Eloquent's query builder, you can achieve this by employing a left join and a whereNull condition. Here's how:

$subquery = DB::table('snapshot')
    ->where('seller_id', '=', 's1.seller_id')
    ->whereRaw('s.created_at < s1.created_at');

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

In this query:

1. The subquery identifies all rows in the snapshot table that have a later created_at timestamp than the current row.
2. The left join combines the main table (s) with the subquery results (s1).
3. The whereNull condition eliminates rows where a corresponding entry in the subquery exists. This effectively filters out all rows except the latest for each seller_id.

By executing this query, you will obtain a collection of the latest rows for each distinct seller_id in the snapshot table.

The above is the detailed content of How to Get the Most Recent Row for Each Seller ID Using Laravel Eloquent?. For more information, please follow other related articles on the PHP Chinese website!