How to Fetch the Latest Snapshot for Each Seller Using Laravel Eloquent?

Eloquent Query for Selecting Latest Rows Grouped by Seller

Given a table consisting of seller-related information including created_at timestamps, it's often necessary to retrieve only the most recent entries for each seller. This task can be accomplished effectively using Laravel Eloquent.

To achieve this, we can employ a MySQL subquery that identifies the latest row for each seller_id using a left join and NULL matching. This subquery can then be used in conjunction with Eloquent to retrieve the desired data.

In SQL, the query would look like this:

<code class="sql">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

Translated into Laravel Eloquent, the query would be written as follows:

<code class="php">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();</code>

This query retrieves the latest snapshot row for each unique seller_id using the subquery, ensuring that the returned results represent the most up-to-date information for each seller.

The above is the detailed content of How to Fetch the Latest Snapshot for Each Seller Using Laravel Eloquent?. For more information, please follow other related articles on the PHP Chinese website!