Laravel Eloquent sum relation's column through other relation

Question

Let's say I have these modules:

• Coupon
• Order (.., coupon_id)
• Item (..., order_id, price)

And there are those relations:

• Coupon Has Many Order.
• Order Has Many Item.

So far so good.

Now I want to sum the price of the items that belongs to the orders that belongs to the coupon. So I thought about doing something like this:

Coupon::orders->items->sum('price');

Well, it won't work because orders() method returns a Collection of the relation:

/**
 * @return \Illuminate\Database\Eloquent\Relations\HasMany
 */
public function orders()
{
    return $this->hasMany(Order::class);
}

I'm getting an error that says item property is protected. I found out that it doesn't related to my Item module, it's just because Collection has protected $items = [];.

So, after I cleared out some facts, my question/problem is, How can I sum the items->price of the orders that belong to Coupon?

---

Of course I can achieve my goal by doing a foreach loop like this:

$price = 0;

foreach (Coupon:orders as $order)
    $price += $order->items->sum('price');

return $price;

But I'm looking for a cleaner solution. Anyone?

Answer 1

A few ways to achieve that:

$coupon_id; // the one you're looking for

Item::whereHas('order', function ($q) use ($coupon_id) {
  $q->where('coupon_id', $coupon_id);
})->sum('items.price');

// OR

// Coupon model
public function items()
{
  return $this->hasManyThrough(Item::class, Order::class);
}

Coupon::find($coupon_id)->items()->sum('price') // aggregate query
Coupon::find($coupon_id)->items->sum('price') // sum on collection

// OR
Coupon::with('orders.items')->find($coupon_id)->orders->pluck('items')->collapse()->sum('price');