How to Retrieve the Latest Records for Each Unique Value in a Column using Laravel Eloquent?

How to Get All Rows with Max Created_at in Laravel Eloquent

In Laravel's Eloquent ORM, you may encounter scenarios where you need to retrieve the latest rows for each distinct value in a particular column. This can be achieved using a combination of subqueries and aggregation functions.

Consider a scenario where you have a database table containing the following columns:

id  seller_id   amount   created_at

Suppose you want to obtain the most recent record for each unique seller_id. Here's how you can accomplish this using a raw SQL query:

<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

In this query, we utilize a left join to compare each record in the snapshot table (aliased as s) with all other records having the same seller_id. We then use the AND condition to filter out records where s.created_at is less than s1.created_at. Finally, we include a WHERE clause to exclude any records with a matching seller_id in the right table (s1), effectively isolating the latest record for each seller_id.

To achieve the same result using the Laravel query builder, you can execute the following code:

<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 builder approach mirrors the logic of the raw SQL query, encapsulating it within Laravel's fluent interface. It returns a collection of the latest records for each distinct seller_id present in the snapshot table.

The above is the detailed content of How to Retrieve the Latest Records for Each Unique Value in a Column using Laravel Eloquent?. For more information, please follow other related articles on the PHP Chinese website!