Laravel Query Builder - Use sum method on a generated attribute

Question

Let's say I have these 2 models:

Order Model:

• id
• state (incomplete, complete)

---

Item Model:

• id
• order_id
• type
• is_worthy.

.

/**
 * Returns the item's price according to its worthy
 */
public function getPriceAttribute()
{
    return $this->is_worthy ? 100 : 10; // $
}

---

So far so good.

Now I want to summarize the price of the complete orders. So I'm doing this:

App\Item::whereHas('order', function ($query) {
    $query->where('state', 'complete');
})->sum('price')

But the thing is, that I don't have in my items table the column price. Because the price attribute is generated in the Model.

So my question is, how can I summarize price of the complete orders?

Answer 1

There are 2 ways to do this:

1. Have PHP do all the work

$items = App\Item::whereHas('order', function ($query) {
    $query->where('state', 'complete');
})->get();
$sum = $items->sum(function($item) {
    return $item->price;
});
// In Laravel 5.4, you can replace the last line with $sum = $items->sum->price;

2. Have SQL do all the work

$items = App\Item::whereHas('order', function ($query) {
    $query->where('state', 'complete');
})->select('*', DB::raw('IF(is_worthy, 100, 10) as price'))->sum('price');