How to calculate the sum of two columns and the number of related relationships in laravel?

Question

I have two tables: buyers and orders. Buyers have many orders.

I want the sum of all buyers and the number of orders and two columns of orders (price, fee) within a specific time period. I can get the count by doing the following,

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

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

Answer 1

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

Answer 2

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

For using withSum({relation},{column}) you need to call it separately for each one.

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() Only available in Laravel version 8 or higher.

EditArrow 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();