laravel 使用 Eloquent 获取连接表的最新值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24343738/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Getting just the latest value on a joined table with Eloquent
提问by Rich Bradshaw
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.
我可以轻松地Product::with('prices')->get();以每个产品的价格购买每种产品。
How can I use Eloquent to only get the most recent price? (Also, what if I wanted the cheapest/most expensive price instead?)
如何使用 Eloquent 仅获取最新价格？（另外，如果我想要最便宜/最贵的价格呢？）
回答by Jarek Tkaczyk
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.
你可以调整你的关系来得到你想要的。接受的答案当然有效，但是对于大量数据可能会造成内存过大。
Here's how to use Eloquent for this:
以下是使用 Eloquent 的方法：
// 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
回答by Unnawut
When Laravel eager loads a relationship, it will perform two queries similar to this:
当 Laravel 预先加载关系时，它会执行两个类似的查询：
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 Constraintsyou 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:
幸运的是，在 Laravel 的Eager Load Constraints 中with()，您可以传递一个以关系名称作为键和一个子查询闭包作为其值的数组，而不是将字符串传递给。像这样：
$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.
注意：您可能会注意到 Laravel 仍会加载每个产品的所有价格。我认为在仍然使用纯粹的 Eloquent 的同时没有办法解决它，因为急切加载工作的方式是在一个查询中获取所有关系记录，因此没有一种简单的方法可以说只获取每个的最新价格产品。
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();
