In Laravel, can we sum related models attribute using withCount method?

Question

I have 3 models related like so : Prize -> TimeSlot -> Winner, with their corresponding relationship methods $prize->hasMany(TimeSlot) and $prize->hasManyThrough(Winner, TimeSlot).

I am trying to count in one eloquent query the number of winners and the number of time slots.

Here is the query :

$prizes = $prizes->withCount(['winners', 'timeSlots' => function ($query) {
    $query->sum('min'); // min is an attribute on TimeSlot
}])->get();

This gives me the number of winners with a winners_count attribute on each prize. I thought it would give me the 'sum number' of time slots for each prize but the query breaks on that part with an error like :

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'prizes.id' in 'where clause' 
(SQL: select sum(`min`) as aggregate from `time_slots` where `time_slots`.`prize_id` = `prizes`.`id`)

The documentation of withCount method only show how to constraint the query (with a where clause for example) but not with an aggregate like sum.

P.S: I already know how to do this with something like :

$prizes = $prizes->with('timeSlots')->withCount('winners')->get();

$numOfWinners = $prizes->sum('winners_count');

$numOfAvailablePrizes = $prizes->sum(function ($prize) {
    return $prize->timeSlots()->sum('min');
});

But i thought the version with one query would save me some useless loops...

Thanks for answers !

Answer 1

Try this

$query->withCount(['timeSlots AS timeslots_sum' => function ($query) {
    $query->select(DB::raw('SUM(min) as timeslots_sum'));
}]);

It will returns

"timeslots_sum" => "123" // total