使用 Eloquent 获取连接表的最新值 [英] Getting just the latest value on a joined table with Eloquent

问题描述

我有两张这样的表:

产品:

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

价格:

+----+-------+------------+---------------------+
| 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');
}

我可以轻松地运行 Product::with('prices')->get(); 以获取每个产品的价格.

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

如何使用 Eloquent 仅获取最新价格?(另外，如果我想要最便宜/最贵的价格呢?)

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

推荐答案

你可以调整你的关系以获得你想要的.接受的答案当然有效，但是对于大量数据可能会造成内存过大.

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.

以下是使用 Eloquent 的方法:

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');
}

还不是很方便:

$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

这篇关于使用 Eloquent 获取连接表的最新值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！