Laravel: Summing an attribute in a nested relation

I want to sum a nested collection.

I have the following tables:

• Venues
• Offers
• Orders

A venue can have many offers and a offer can have many orders.

Example data may be:

Venues

id        |  name
==========================
5         |  Pizza 4 Less
10        |  Poundland

Offers

id | venue_id | name
==================================
17 | 5        | Buy 1 get one free 
24 | 5        | 30% off pizza
32 | 10       | 50% off
50 | 10       | 20% off

Orders

id | offer_id | bill | paid
===========================
85 | 17       | 15   | true
86 | 17       | 20   | true
87 | 17       | 90   | true
88 | 24       | 14   | true
89 | 32       | 15   | true
90 | 32       | 65   | true
91 | 50       | 24   | true
92 | 50       | 1000 | false

I want to use Laravel Elqouent model to get the total amount paid for each venue. So for the above data I want to get the following result:

id | name          | total_paid
===============================
5  | Pizza 4 Less  | 139
10 | Poundland     | 104

Note that the totals did not include orders that was not paid (i.e. order 92)

The way I currently do this is as follows:

$venues = Venue::with(['offers.orders' => function ($query) {
        $query->where('paid', '=', true);
    }])
    ->get();

$totals = [];
foreach ($venues as $venue) {
    $totalPaid = 0;
    foreach ($venue->offers as $offer) {
        $totalPaid += $offer->orders->sum('bill');
    }
    $totals[$venue->name] = $totalPaid;
}

As you can see, the above code is inefficient and long.

Is there a better way to do this?

from Newest questions tagged laravel-5 - Stack Overflow http://ift.tt/2rsnO31
via IFTTT