My relationships look like this:
So a user can have many Courses through their Roles. Some courses will be duplicates in that collection, though, because Courses will exist for multiple Roles.
So I have to use $user->courses()->distinct()->get() to get a unique list of the actual courses the user has to participate in.
The question is, how do I get distinct related models when using aggregates?
User::withSum('courses as hours_of_training', 'duration');
This will give me the sum of the duration of all related courses (including dupes). How do I get rid of the dupes in the aggregate? A solution on how to do this for withCount exists here.
I solved this by calling the withAggregate function directly and passing the relation as in array in the style ['related_table as alias' => callback] where the callback does the actual aggregation using the query builder.
The second argument of withAggregate needs to be passed, but can be null in this case. It will only be used to build the alias name if none is given.
E.g.
User::withAggregate(['courses as hours_of_training' => function ($query) {
$query->select(DB::raw('SUM(DISTINCT(courses.id))'));
}], null);