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:

select s.*
from snapshot s
left join snapshot s1 on s.seller_id = s1.seller_id
and s.created_at 
Translated into Laravel Eloquent, the query would be written as follows:
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 whereNull('s1.seller_id')
  ->get();
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.