How to Retrieve the Most Recent Record for Each Unique Value in Laravel Eloquent?

Querying for Most Recent Records with Laravel Eloquent

To retrieve the latest record for each unique value in a given table column, Laravel's Eloquent ORM provides a robust solution. Let's walk through a scenario and its eloquent solution:

Scenario:

Consider a table named "snapshot" with columns such as "id", "seller_id", "amount", and "created_at". The goal is to retrieve the most recent row for each distinct "seller_id".

Eloquent Query:

$latestSales = Snapshot::select('seller_id')
    ->groupBy('seller_id')
    ->havingRaw('MAX(created_at)')
    ->orderBy('created_at', 'DESC')
    ->get();

Explanation:

• groupBy('seller_id'): Groups the results based on the "seller_id" column, organizing rows with matching seller IDs together.
• havingRaw('MAX(created_at)'): Filters the results to include only rows that have the maximum "created_at" value within each seller ID group.
• orderBy('created_at', 'DESC'): Orders the results within each seller ID group in descending order of "created_at", ensuring the latest record appears first.
• get(): Fetches the results from the database.

Alternate Query (Raw Query):

If preferred, a raw SQL query can be utilized to achieve the same result:

SELECT * FROM snapshot
WHERE (seller_id, created_at) IN (
    SELECT seller_id, MAX(created_at) AS max_created_at
    FROM snapshot
    GROUP BY seller_id
)
ORDER BY created_at DESC;

This query identifies the latest row for each "seller_id" using a subquery to determine the maximum "created_at" value within each group. Rows with matching pairs of "seller_id" and "created_at" (representing the latest records) are then selected and ordered by "created_at" in descending order, resulting in a list of the most recent records for each seller.

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