联接表记录不存在的Active Record [英] Active Record where join table record doesn't exist

问题描述

我正在尝试获取联接表中不存在的所有记录的列表。

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

模型是User，Game和MarkedGame，用户可以在其中将游戏标记为已玩。这是多对多的关系：

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

但是我不喜欢这给我留下了数组而不是Active Record关系对象。加上似乎应该有一种更高性能的方法。

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.

如果没有Active Record处理此方法的方法，也许有SQL方法吗？我的原始SQL并不是特别强大，因此将不胜感激。

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.

谢谢。

推荐答案

应该这样做。返回当前用户未标记的所有游戏。

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)

这篇关于联接表记录不存在的Active Record的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！