如何使用Laravel Eloquent使用groupBy和havingRaw查询最少数量的相关模型

Question

I want to select a user only if they have more than 10 trips, where the trip must have a walking leg.

Table structure

// users
(int) id

// trips
(int) id
(fk) user_id

// legs
(int) id
(fk) trip_id
(str) mode

Models

class User extends Model
{
    public function trips()
    {
        return $this->hasMany(Trip::class);
    }
}

class Trip extends Model
{
    public function legs()
    {
        return $this->hasMany(Leg::class);
    }
}

I can return a user that has trips where there is a walking leg ...

$user = User::where('id', '=', 1)
    ->whereHas('trips', function($query) {
        ->query('legs', function($query) {
            $query->whereHas('mode', '=', 'walking');
        });
    })
    ->first();

... and it seems that it can be done using groupBy() and havingRaw('COUNT(*) > 10') ...

$query = User::whereHas('trips', function ($query) {
    $query
        ->whereHas('legs', function ($query) {
            $query->where('mode', '=', 'walking');
        })
        ->groupBy('id')
        ->havingRaw('COUNT(*) > 10');
});

... but this is where I'm stuck.

Any help much appreciated, thanks!

Answer 1

Do this

$user = User::where('id', 1)
    ->whereHas('trips', function($query) {
        $query->whereHas('legs', function ($query) {
            $query->where('mode', 'walking');
        });
    }, '>', 10)
    ->first();

whereHas works just like has with a closure for conditions.

public function whereHas($relation, Closure $callback = null, $operator = '>=', $count = 1);