[Question] Retrieve a list of models, with an aggregated sum of a relationship property.

[fidan-mkdir]

First off all, I am sorry if this is the wrong place to ask this question, but I've been going in circles about this issue that I've had with Mongo DB.

I have a User model which hasMany Shifts and the shift has an int property called 'duration', start and end date. What I need to do is take all of the users based on some condition with the total duration of shifts for a given period. With SQL, I could to this by adding a select, something like:

$result = \App\User::addSelect(
        [
            'total_hours' => App\Shift::selectRaw('SUM(minutes)')
                ->whereColumn('user_id', 'users.id')
                ->whereBetween('datetime', ['2020-01-01 00:00:00','2020-01-31 23:59:59',])
                ->limit(1)
        ]
    )
    ->havingRaw('total_hours > ?', [10])
    ->get();

Right now I have a method on the User model that takes a start, end date and calculates the duration of shifts within the given time.

public function total_hours_in_period(Carbon $start, Carbon $end) {
    return $this->shifts()->where('start', '>=', $start)->where('start', '<', $end)->sum('duration');
}

But in order to use it I have to first retrieve the users then loop them and call that method which is not what I want. I want to do it all in one query.

Is there anything that I can use to get this functionality with Moloquent without having to switch to a completely raw query for the entire thing. I kinda need it using the query builder.