How to pull sum of two columns along with the count of a relation in laravel?

Question

I have two tables: buyers and orders. A buyer has many orders.

I want all the buyers along with the orders count and sum of two columns (price, charge) of orders for specific period of time. I can get the count by doing the following,

Buyer::withCount([
    'orders' => fn ($query) => $query
        ->where('created_at', '>=', $dateTo)
])
->get();

But how do I get the sum for specified period of time (->where('created_at', '>=', $dateTo))?

@miken32 it's not really a duplicate question since the answer you're referring to is an independent query which in this case will need a loop on every result to use it while this question ask for it to be eager loaded with the result via the method `withCount()` of Eloquent. — N69S, May 20 '22 at 15:22

N69S · Accepted Answer · 2022-05-20T15:28:15.163

2

To use a variable inside a closure, you need to use use() to send its value to that function.

For the use of withSum({relation},{column}) you need ro make a separate call for each.

Buyer::withCount([
        'orders' => function ($query) use ($dateTo) { 
            $query->where('created_at', '>=', $dateTo);
        }
    ])
    ->withSum([
        'orders' => function ($query) use ($dateTo) { 
            $query->where('created_at', '>=', $dateTo);
        }
    ], 'price')
    ->withSum([
        'orders' => function ($query) use ($dateTo) { 
            $query->where('created_at', '>=', $dateTo);
        }
    ], 'charge')
    ->get();

withSum() is only available in Laravel version 8 or higher.

Edit for arrows function syntax

Buyer::withCount([
        'orders' => fn ($query) $query->where('created_at', '>=', $dateTo),
    ])
    ->withSum(['orders' => fn ($query) $query->where('created_at', '>=', $dateTo)], 'price')
    ->withSum(['orders' => fn ($query) $query->where('created_at', '>=', $dateTo)], 'charge')
    ->get();

edited May 20 '22 at 15:28

answered May 20 '22 at 13:15

N69S

16,110
3
22
36

you cannot have a `use()` on php arrow function. You code won't compile. – ml59 May 20 '22 at 13:33
@ml59 there, fixed it – N69S May 20 '22 at 14:29
1

Why not just leave the arrow functions in place, as in the question? Much neater. – miken32 May 20 '22 at 14:32
@miken32 it is up to preference, and I'd rather use this format as it is more maintainable and uses less memory (that is why you dont need to use `use($dateTo)` for arrow functions) – N69S May 20 '22 at 15:09
[citation needed] – miken32 May 20 '22 at 15:10
@miken32 you're right since php 7 unused variables event if available are not used in the compiled code so no memory usage difference. I still stand by the preference view point as I personally find it more maintainable. – N69S May 20 '22 at 15:17

score 0 · Answer 2 · answered May 20 '22 at 13:17

In the same way you have withSum(), here is how you can do it

Buyer::withCount([
            'orders' => fn($query) => $query->where('created_at', '>=', $dateTo),
        ])->withSum([
            'orders' => fn($query) => $query->where('created_at', '>=', $dateTo),
        ], 'price')->withSum([
            'orders' => fn($query) => $query->where('created_at', '>=', $dateTo),
        ], 'charge')->get();

How to pull sum of two columns along with the count of a relation in laravel?

2 Answers2