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 l…

Accepted Answer

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
```

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

Solution

Problem