How to join relationships in Eloquent?

I’m stuck in a problem, I have these 3 models: Order, OrderItem and Product, each Order has many Order Items, and each Order Item has one Prduct but a Product can be ordered several times.

Product has a supplier ID column (each supplier has many Products).

So here are the relationships:

Model: Order:

public function items(){
    
return $this->hasMany('AppModelsOrderItem','id','order_id');
}

Model OrderItem

  public function order(){

  return $this->belongsTo('AppModelsOrder','order_id','id');
 }
  public function product(){
  
    return $this->belongsTo('AppModelsProduct','item_id','id');
  }

Model Product

public function orders()
{
return $this->hasMany('AppModelsOrderItem','item_id','id');
}

What I want is that given a supplier_id, I need to filter Products for that supplier_id, and get the Order Items for that supplier_id and then group them by created date which is only available in the Order Model and not Order Items.

In other words, I want to get all Orders of a given supplier (through the supplier_id column in products) grouped by their creation date but there is no direct relation from Orders to Products I have to get Order Items to get to Products (Orders > Order Items > Products).

I thought about doing the following:

Product::with("orders")->where("supplier_id","=",$supplier_id)->join("orders","orders.id","=","products.orders")

The problem is that products.orders is a relationship (between Product and Order Item) and not a column.

Answer

You can do it using DB interface or if you’d like continue using Models you need to use multiple pluck.

You can do something like this:

Product::with(['orders','orders.order'])->where('supplier_id', $supplier_id)->get()->pluck("orders")->flatten()->pluck('order');

Advertisement

Answer