So I have a data structure as follows:

User

• id

Collection

• user_id
• game_id

Game

• id
• status

In my models I have the relationships as such:

User::hasMany(Collection)
Collection::belongsTo(Game)
Game::hasMany(Collection)

So what I'm trying to do is get a list of users and include all associated games via the collection table. But I want to include only those games that have a particular status. The code I'm using (which isn't working) is as follows:

User::with('collections.game')->whereHas('collections.game', function ($query) {
    return $query->where('status', '<>', 'denied');
})->get()

My problem is that all game records are getting returned, including those with the denied status. I was reading around and it seems like the above should work but it isn't.

What am I doing wrong?

CodePudding user response：

You can't do whereHas('collections.game', ...). You must do a whereHas of a whereHas:

User::with('collections.game')
    ->whereHas('collections', function (Builder $query) {
        return $query->whereHas('game', function (Builder $query) {
            return $query->where('status', '<>', 'denied');
        })
    })
    ->get()

CodePudding user response：

I believe you will need a filter on your with() clause because whereHas() will filter users data only but the eager loaded relations will not have that filter included automatically.

User::with(['collections.game' => function ($query) {
        return $query->where('status', '<>', 'denied');
    }])
    ->whereHas('collections.game', function ($query) {
        return $query->where('status', '<>', 'denied');
    })->get()