Getting just the latest value on a joined table with Eloquent

Question

I have two tables like this:

products:

+----+-----------+
| id |   name    |
+----+-----------+
|  1 | Product 1 |
|  2 | Product 2 |
|  3 | Product 3 |
|  4 | Product 4 |
+----+-----------+

prices:

+----+-------+------------+---------------------+
| id | price | product_id |     created_at      |
+----+-------+------------+---------------------+
|  1 |    20 |          1 | 2014-06-21 16:00:00 |
|  2 |    10 |          1 | 2014-06-21 17:00:00 |
|  3 |    50 |          2 | 2014-06-21 18:00:00 |
|  4 |    40 |          2 | 2014-06-21 19:00:00 |
+----+-------+------------+---------------------+

I have this relationship on Product:

public function prices()
{
    return $this->hasMany('Price');
}

I can easily run Product::with('prices')->get(); to get each product with each of the prices it's had.

How can I use Eloquent to only get the most recent price? (Also, what if I wanted the cheapest/most expensive price instead?)

Mccleary

Answer 1

You can tweak your relations to get what you want. Accepted answer of course works, however it might be memory overkill with lots of data.

Find out more here and there.

Here's how to use Eloquent for this:

// Product model
public function latestPrice()
{
   return $this->hasOne('Price')->latest();
}

// now we're fetching only single row, thus create single object, per product:
$products = Product::with('latestPrice')->get();
$products->first()->latestPrice; // Price model

That's nice, but there's more. Imagine you'd like to load highest price (just a value) for all the products:

public function highestPrice()
{
   return $this->hasOne('Price')
      ->selectRaw('product_id, max(price) as aggregate')
      ->groupBy('product_id');
}

Not very convenient yet:

$products = Product::with('highestPrice')->get();
$products->first()->highestPrice; // Price model, but only with 2 properties
$products->first()->highestPrice->aggregate; // highest price we need

So add this accessor to make the life easier:

public function getHighestPriceAttribute()
{
    if ( ! array_key_exists('highestPrice', $this->relations)) $this->load('highestPrice');

    $related = $this->getRelation('highestPrice');

    return ($related) ? $related->aggregate : null;
}

// now it's getting pretty simple
$products->first()->highestPrice; // highest price value we need

Bicentenary

Do it by redefining the relationship itself. That's pretty neat. Voting up! – Courageous

This is really great, and I learnt a lot. Where can I find documentation about 'latest()'? I checked laravel.com/api/class-Illuminate.Database.Eloquent.Model.html and couldn't see any reference to it there. – Mccleary

It's method on the base query builder. Check here: laravel.com/api/4.2 on the Illuminate\Database\Query\Builder class. – Bicentenary

Awesome - must have been looking at older docs. If you have a moment, how can I use this when I have a joining table - I also have a locations table, and a products_locations table. Products has a belongsToMany relationship with locations. To get the latest Location, adding a hasOne('Location')->latest() doesn't work - is it an easy fix? – Mccleary

No such easy way for many-to-many relationship. The only thing I would suggest is adjusting accessor for easy accessing first model on the collection. – Bicentenary

A very elegant solution. – Andean

in this way, how do we can get more than one record ? – Nicholson

Answer 2

When Laravel eager loads a relationship, it will perform two queries similar to this:

SELECT * FROM products WHERE 1;
SELECT * FROM prices WHERE product_id = 1;

What you want to do is to add a condition to the second query to get the row with most recent price. So you would want something like this:

SELECT * FROM products WHERE 1;
SELECT * FROM prices WHERE product_id = 1 ORDER BY price;

Luckily in Laravel's Eager Load Constraints you can, instead of passing a string into with(), you can pass an array with the relationship name as key and a subquery closure as its value. Like this:

$products = Product::with(array('prices' => function($query)
{
    $query->orderBy('created_at', 'desc');
}))->get();

Then in your code you can do:

$product->prices->first();

to get the most recent price of each product.

Note: You may notice that Laravel will still load all the prices for each product. I don't think there's a way around it while still using purely Eloquent because the way eager loading work is fetching all the relationship records in one single query, so there isn't an easy way to say get only the most recent price for each product.

---

Another solution:

However, if you are strictly needing to know just a value from another table, you could do a sub-select instead:

$products = Product::select('*')
    ->addSelect(DB::raw('(SELECT price FROM prices WHERE products.id = prices.product_id ORDER BY created_at DESC LIMIT 1) price'))
    ->get();

Courageous

Or join them. Product::join('prices', 'prices.product_id', '=', 'products.id')->orderBy('prices.somefield', 'asc/desc')->first(); – Waite

Is there a way to make it work for querying a set of products though? – Courageous

Yes, just add a where clause as normal. ->where('products.field', 'condition', 'value') etc. – Waite

But your query is still limited to 1 result by ->first(); no? I'm not quite sure ->get() would work. I think you would get multiple rows of each product. – Courageous

Yes, that's the point. The question explicitly asks for the latest row, the most expensive, etc. - hence the first() call. Edit: Maybe I misread the question. If OP means to get all products but with only the latest price for each, then my query will indeed not work. :) – Waite

Ah right, I see your point. I interpreted the question differently. – Courageous

Thanks Unnawut - great answer and was very useful. Joel - yeah, I did mean for all products but with the highest/newest price - thanks for the suggestion though, that is useful as I wasn't aware you could do joins using Eloquent directly. – Mccleary