CODEWITHSUNDEEP
phplaraveleloquent
Souames
Souames

Reputation: 1145

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('App\Models\OrderItem','id','order_id');
}

Model OrderItem

  public function order(){

  return $this->belongsTo('App\Models\Order','order_id','id');
 }
  public function product(){
  
    return $this->belongsTo('App\Models\Product','item_id','id');
  }

Model Product

public function orders()
{
return $this->hasMany('App\Models\OrderItem','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.

Upvotes: 0

Views: 90

Answers (2)

manou.MHD
manou.MHD

Reputation: 38

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

Upvotes: 1

Mohammadamd
Mohammadamd

Reputation: 31

Use following snippet:

Product::select(DB::Raw('DATE(created_at) as cr'), ...)->where('supplier_id', $supplier_id)->with('orders.order')->groupBy('supplier_id')->get();

After that, you can loop on the result and group them by date:

$result->groupBy('cr')

Upvotes: 1

Related Questions