Retrieving only the last value in a joined table with Eloquent

Question

I have two tables:

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 attitude to the Product:

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

I can easily run Product::with('prices')->get(); to get every product with every price.

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

Answer 1

You can customize your relationship to get what you want. The accepted answer, of course, works, but it can be an excessive amount of memory with a lot of data.

More here and there .

Here's how to use Eloquent to do 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 

This is good, but there is more. Imagine that you would like to download the highest price (just value) for all products:

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

Not very comfortable:

 $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 accessory to make 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 getting pretty simple $products->first()->highestPrice; // highest price value we need 

Answer 2

When Laravel seeks to load relations, he will execute two requests similar to this:

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

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

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

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

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

Then in the code you can:

 $product->prices->first(); 

to get the latest price of each product.

Note. You may notice that Laravel will still download all prices for each product. I don’t think there is a way around it while still using purely Eloquent, because working with impatience loads all relationship records in one request, so there is no easy way to say to get only the latest price for each product.

---

Another solution:

However, if you strictly need to know only the value from another table, you can make a sub-selection:

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