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?)

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

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();