Laravel Eloquent -> get only events where city is

Question

I have a table with:

Events
Cities
Clubs

The relations are as following:

• An Event 'hasOne' Club, and a Club 'hasMany' Events.
• A Club 'hasOne' City and a City 'hasMany' Clubs.

I want to add a filter to my query to get all events where the club.city == 'Oklahoma'. This is what I tried:

$events = Event::where( 'accepted', 1 )
                ->with( [ 'club.city', 'organisation' ] )
                ->where( 'club.city.name', 'Rotterdam' )
                ->orderBy( 'created_at', 'asc' )
                ->paginate( 6 );

Answer 1

You can use whereHas to filter based on existance of a condition on a relationship.

$events = Event::where( 'accepted', 1 )
    ->with( [ 'club.city', 'organisation' ] )
    ->whereHas('club.city', function ($q) use ($cityname) {
        $query->where('name', $cityname);
    })->orderBy( 'created_at', 'asc' )
    ->paginate( 6 );