Laravel: one to many to many, extract excellent () values

Question

Laravel 4 project using Eloquent ORM.

I have three tables: customers, orders and products (+ 1 pivot table order_product). Customers are tied to each other in orders. Orders are tied to many-to-many products.

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

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

To better understand this, suppose foods are consumed.

For example, Client No. 1 may place:

• Order No. 1 for products A, B and C;
• Order No. 2 for products A, C and D;
• Order No. 3 for Products C and E;

... and the result that I want to get is a collection with products A, B, C, D and E.

Models (pseudo-encoded 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 ??? } } 

Answer 1

Thanks to @deczo's answer, I was able to set up a single request method to extract the elements:

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

Answer 2

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

 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 have included the last two parameters, but if you follow the unique_id pattern, they may be omitted.

Answer 3

I cannot come up with a simple communication method for this, but here is 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); 

Answer 4

@Deczo's answer probably works fine and is probably much more productive, since all data reduction is done in the database itself, but it uses the “pure Laravel” method, which is undoubtedly more readable:

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

Please 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 cannot access it as a property, like in a relationship (that is, you cannot do $products = $customer->products; ).

In addition, I understand a little of my understanding of the Illuminate\Database\Eloquent\Collection#merge() method that it automatically performs a DISTINCT -like function. If not, you will have to do $collection->unique() a kind of thing.