Active Record where join table record doesn't exist

Question

I am trying to get a list of all records that don't exist in a join table.

The models are User, Game and MarkedGame, where users can mark games as played. It's a many to many relationship:

User > MarkedGame < Game

What I want is a list of all games that haven't been marked by the user.

I know that I could do two separate queries and subtract them:

Game.all - current_user.games

But I don't like that this leaves me with an array rather than an Active Record relation object. Plus it seems like there should be a more performant way of doing it.

If there is no Active Record way of handling this, is there perhaps a SQL way? My raw SQL is not particularly strong so any help on that would be appreciated.

Thanks.

Answer 1

You can try the following:

Game.where.not(id: MarkedGame.where(user_id: current_user.id).pluck(:game_id))

Answer 2

That should do it. Returns all games that have not been marked by the current user.

Game.where('id not in (select game_id from marked_games where user_id = ?)', current_user.id)