How to Efficiently Select the Latest Transaction for Each Seller Using Laravel Eloquent?

Laravel Eloquent: Selecting All Latest Rows for Each seller_id

Consider a table that contains transaction records:

• id
• seller_id
• amount
• created_at

The objective is to retrieve the latest transaction for each unique seller_id. While it's straightforward to obtain the most recent transaction for a specific seller, the challenge lies in extracting the latest records for all sellers.

Database Query Approach:

One efficient method is to utilize a SQL 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

This query identifies the most recent transaction for each seller_id by employing a self-join and excluding records with a later created_at time.

Laravel Query Builder Approach:

To achieve the same result using Laravel Query Builder:

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

This code employs a similar self-join technique as the SQL query, returning the latest transaction for each seller_id.

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