Laravel：一对多对多，检索不同的（）值

Laravel: One to Many to Many, retrieve distinct() values

提问by ElementalStorm

Laravel 4 Project, using Eloquent ORM.

Laravel 4 项目，使用 Eloquent ORM。

I have three tables: customers, orders and products (+ 1 pivot table order_product). Customers are linked one-to-many to Orders. Orders are linked many-to-many to Products.

我有三个表：客户、订单和产品（+ 1 个数据透视表 order_product）。客户与订单一对多链接。订单多对多链接到产品。

Customers  1-->N  Orders  N<-->N   Products

I would like to have a method on Customer model that retrieves a list of products that customer is buying.

我想在 Customer 模型上有一个方法来检索客户正在购买的产品列表。

To better understand this, assume products are consumable.

为了更好地理解这一点，假设产品是消耗品。

For example Customer #1 can place:

例如，客户 #1 可以放置：

• Order #1 for Products A, B and C;
• Order #2 for Products A, C and D;
• Order #3 for Products C and E;

• 产品 A、B 和 C 的订单 #1；
• 产品 A、C 和 D 的订单 #2；
• 产品 C 和 E 的订单 #3；

...and the result I want to retrieve is a Collection with Products A, B, C, D and E.

...我想要检索的结果是一个包含产品 A、B、C、D 和 E 的集合。

Models are (pseudo-coded on the fly):

模型是（动态伪编码）：

class Product extends Eloquent {

    public function orders()
    {
        return $this->belongsToMany('Order');
    }

}

class Orders extends Eloquent {

    public function customer()
    {
        return $this->belongsTo('Customer', 'customer_id');
    }

    public function products()
    {
        return $this->belongsToMany('Product');
    }

}

class Customers extends Eloquent {

    public function orders()
    {
        return $this->hasMany('Orders', 'customer_id');
    }

    public function products()
    {
        // What to put here ???
    }

}

采纳答案by ElementalStorm

Thanks to @deczo's answer, I was able to put up a single query method to retrieve items:

感谢@deczo 的回答，我能够建立一个查询方法来检索项目：

public function items()
{
    $query = DB::table('items')->select('items.*')
        ->join('item_order', 'item_order.component_id', '=', 'items.id')
        ->leftJoin('orders', 'item_order.order_id', '=', 'orders.id')
        ->leftJoin('customers', 'customers.id' , '=', 'orders.customer_id')
        ->where('customers.id', $this->id)
        ->distinct()
        ->orderBy('items.id');

    $eloquent = new Illuminate\Database\Eloquent\Builder( $query );
    $eloquent->setModel( new Item );
    return $eloquent->get();
}

回答by Mei Gwilym

This is a Many-to-Many relationship, but with the Orderstable as the pivot table.

这是一个多对多关系，但以Orders表作为数据透视表。

class Customers extends Eloquent {

    public function orders()
    {
        return $this->hasMany('Orders', 'customer_id');
    }

    public function products()
    {
        return $this->belongsToMany('Products', 'orders', 'customer_id', 'product_id');
    }
}

I've included the last two parameters, but if you follow the singular_id pattern they can be left out.

我已经包含了最后两个参数，但是如果您遵循 single_id 模式，它们可以被排除在外。

It's possible to receive distinct Product models like this:

可以接收不同的 Product 模型，如下所示：

public function products()
{
    return $this->belongsToMany('Products', 'orders', 'customer_id', 'product_id')
        ->distinct();
}

回答by Jarek Tkaczyk

I can't think of easy relation method for this one, but here's a workaround:

我想不出简单的关系方法，但这里有一个解决方法：

$productsIds = DB::table('customers')
    ->leftJoin('orders', 'orders.customer_id', '=', 'customers.id')
    ->join('order_item', 'order_item.order_id', '=', 'orders.id')
    ->leftJoin('items', 'order_item.item_id' , '=', 'items.id')
    ->distinct()
    ->get(['items.id']);

$productsIds = array_fetch($productsIds, 'id');

$productsCollection = Product::whereIn('id', $productsIds);

回答by alexrussell

@deczo's answer probably works fine, and is probably a lot more performant as all the data reduction is done in the database itself, but here's a 'pure Laravel' way that's undoubtedly more readable:

@deczo 的答案可能工作正常，并且可能性能更高，因为所有数据缩减都是在数据库本身中完成的，但这是一种“纯 Laravel”方式，无疑更具可读性：

use Illuminate\Database\Eloquent\Collection;

class Customer extends Eloquent
{
    ...
    public function products()
    {
        $products = new Collection;
        foreach ($this->orders as $order) {
            $products = $products->merge($order->products);
        }

        return $products;
    }
}

Note that this method will not act like normal relationship methods - to get the resulting collection you call the method (i.e. $products = $customer->products();) and you can't access it as a property like you can with relationships (i.e. you can't do $products = $customer->products;).

请注意，此方法不会像正常的关系方法一样运行 - 要获取结果集合，您调用该方法（即$products = $customer->products();），并且您不能像使用关系那样将其作为属性访问（即您不能这样做$products = $customer->products;）。

Also, I'm kinda going on my understanding of the Illuminate\Database\Eloquent\Collection#merge()method here that it automatically does a DISTINCT-like thing. If not, you'll have to do a $collection->unique()kinda thing.

另外，我有点继续我对Illuminate\Database\Eloquent\Collection#merge()这里方法的理解，它会自动做类似DISTINCT的事情。如果没有，你将不得不做一些$collection->unique()事情。