How to limit the records returned to be distinct by a column value?

Say I have a hasMany relationship on my User model named vehicles. And the Vehicle model has the following columns: id, user_id, type, purchased_on. Lets say I created related vehicle models with the following data:

["type" => "bike", "purchased_on" => 12-25-2025],
["type" => "bike", "purchased_on" => 12-25-2020],
["type" => "bike", "purchased_on" => 12-25-2015],
["type" => "car", "purchased_on" => 12-25-2010],
["type" => "car", "purchased_on" => 12-25-2005],
["type" => "boat", "purchased_on" => 12-25-2000],

So I have three bikes (purchased 5 years apart), two cars (purchased 5 years apart), and just one boat.

I want to make a query where I get my most recent vehicles by type. So limiting the results to at most one of each type, and returning the most recent of each of those types. So the results would be three records (one bike, one car, and one boat) as such:

["type" => "bike", "purchased_on" => 12-25-2025],
["type" => "car", "purchased_on" => 12-25-2010],
["type" => "boat", "purchased_on" => 12-25-2000],

Is this possible to do with eloquent, or do I need to load them all and then manually filter the collection down to what I'm looking for?

Thanks!

RemiM

1 year ago

Best Answer

Level 16

Yes, you can use a subquery to get the latest vehicule per type:

$vehicles = Vehicle::whereIn('id', function ($query) use ($userId) {
    $query->selectRaw('MAX(id)')
        ->from('vehicles')
        ->where('user_id', $userId)
        ->groupBy('type');
})->get();

Note: MAX(id) works if IDs are sequential and newer vehicles have higher IDs.

amorphia

1 year ago

Level 1

@RemiM Ah, of course. My mind never goes to subqueries, but as soon as you pointed it out it makes perfect sense. Thanks much!

Please or to participate in this conversation.