Ruby-on-rails 如何使用 ActiveRecord/Rails 表达 NOT IN 查询？

How to express a NOT IN query with ActiveRecord/Rails?

提问by Toby Joiner

Just to update this since it seems a lot of people come to this, if you are using Rails 4 look at the answers by Trung Lê` and VinniVidiVicci.

只是为了更新这个，因为似乎有很多人来这里，如果您使用 Rails 4，请查看 Trung Lê` 和 VinniVidiVicci 的答案。

Topic.where.not(forum_id:@forums.map(&:id))

Topic.where(published:true).where.not(forum_id:@forums.map(&:id))

I'm hoping there is a easy solution that doesn't involve find_by_sql, if not then I guess that will have to work.

我希望有一个不涉及的简单解决方案find_by_sql，如果没有，那么我想这将必须起作用。

I found this articlewhich references this:

我发现这篇文章引用了这个：

Topic.find(:all, :conditions => { :forum_id => @forums.map(&:id) })

which is the same as

这与

SELECT * FROM topics WHERE forum_id IN (<@forum ids>)

I am wondering if there is a way to do NOT INwith that, like:

我想知道是否有办法NOT IN解决这个问题，例如：

SELECT * FROM topics WHERE forum_id NOT IN (<@forum ids>)

回答by José Castro

Rails 4+:

轨道 4+：

Article.where.not(title: ['Rails 3', 'Rails 5']) 

Rails 3:

轨道 3：

Topic.where('id NOT IN (?)', Array.wrap(actions))

Where actionsis an array with: [1,2,3,4,5]

actions一个数组在哪里：[1,2,3,4,5]

回答by Trung Lê

FYI, In Rails 4, you can use notsyntax:

仅供参考，在 Rails 4 中，您可以使用not语法：

Article.where.not(title: ['Rails 3', 'Rails 5'])

回答by jonnii

You can try something like:

您可以尝试以下操作：

Topic.find(:all, :conditions => ['forum_id not in (?)', @forums.map(&:id)])

You might need to do @forums.map(&:id).join(','). I can't remember if Rails will the argument into a CSV list if it is enumerable.

你可能需要做@forums.map(&:id).join(',')。我不记得 Rails 是否会将参数放入 CSV 列表（如果它是可枚举的）。

You could also do this:

你也可以这样做：

# in topic.rb
named_scope :not_in_forums, lambda { |forums| { :conditions => ['forum_id not in (?)', forums.select(&:id).join(',')] }

# in your controller 
Topic.not_in_forums(@forums)

回答by Pedro Rolo

Using Arel:

使用阿雷尔：

topics=Topic.arel_table
Topic.where(topics[:forum_id].not_in(@forum_ids))

or, if preferred:

或者，如果愿意：

topics=Topic.arel_table
Topic.where(topics[:forum_id].in(@forum_ids).not)

and since rails 4 on:

并且因为 Rails 4 在：

topics=Topic.arel_table
Topic.where.not(topics[:forum_id].in(@forum_ids))

Please notice that eventually you do not want the forum_ids to be the ids list, but rather a subquery, if so then you should do something like this before getting the topics:

请注意，最终您不希望 forum_ids 成为 ids 列表，而是一个子查询，如果是这样，那么您应该在获取主题之前执行以下操作：

@forum_ids = Forum.where(/*whatever conditions are desirable*/).select(:id)

in this way you get everything in a single query: something like:

通过这种方式，您可以在单个查询中获得所有内容：例如：

select * from topic 
where forum_id in (select id 
                   from forum 
                   where /*whatever conditions are desirable*/)

Also notice that eventually you do not want to do this, but rather a join - what might be more efficient.

另请注意，最终您不想这样做，而是希望加入 - 这样做可能更有效。

回答by Vincent Cadoret

To expand on @Trung Lê answer, in Rails 4 you can do the following:

要扩展@Trung Lê 答案，在 Rails 4 中，您可以执行以下操作：

Topic.where.not(forum_id:@forums.map(&:id))

And you could take it a step further. If you need to first filter for only published Topics and thenfilter out the ids you don't want, you could do this:

你可以更进一步。如果您需要先过滤仅已发布的主题，然后过滤掉您不想要的 id，您可以这样做：

Topic.where(published:true).where.not(forum_id:@forums.map(&:id))

Rails 4 makes it so much easier!

Rails 4 让它变得如此简单！

回答by Filipe Giusti

The accepted solution fails if @forumsis empty. To workaround this I had to do

如果@forums为空，则接受的解决方案失败。为了解决这个问题，我必须做

Topic.find(:all, :conditions => ['forum_id not in (?)', (@forums.empty? ? '' : @forums.map(&:id))])

Or, if using Rails 3+:

或者，如果使用 Rails 3+：

Topic.where( 'forum_id not in (?)', (@forums.empty? ? '' : @forums.map(&:id)) ).all

回答by jake

Most of the answers above should suffice you but if you are doing a lot more of such predicate and complex combinations check out Squeel. You will be able to doing something like:

上面的大多数答案应该足够你了，但如果你正在做更多这样的谓词和复杂的组合，请查看Squeel。您将能够执行以下操作：

Topic.where{{forum_id.not_in => @forums.map(&:id)}}
Topic.where{forum_id.not_in @forums.map(&:id)} 
Topic.where{forum_id << @forums.map(&:id)}

回答by Marcin Wyszynski

You may want to have a look at the meta_where pluginby Ernie Miller. Your SQL statement:

您可能想看看Ernie Miller的meta_where 插件。您的 SQL 语句：

SELECT * FROM topics WHERE forum_id NOT IN (<@forum ids>)

...could be expressed like this:

...可以这样表达：

Topic.where(:forum_id.nin => @forum_ids)

Ryan Bates of Railscasts created a nice screencast explaining MetaWhere.

Railscasts 的 Ryan Bates 创建了一个很好的截屏视频来解释 MetaWhere。

Not sure if this is what you're looking for but to my eyes it certainly looks better than an embedded SQL query.

不确定这是否是您要查找的内容，但在我看来，它肯定比嵌入式 SQL 查询更好。

回答by Andy Triggs

The original post specifically mentions using numeric IDs, but I came here looking for the syntax for doing a NOT IN with an array of strings.

最初的帖子特别提到了使用数字 ID，但我来这里是为了寻找使用字符串数组执行 NOT IN 的语法。

ActiveRecord will handle that nicely for you too:

ActiveRecord 也会很好地为您处理：

Thing.where(['state NOT IN (?)', %w{state1 state2}])

回答by Omar Qureshi

Can these forum ids be worked out in a pragmatic way? e.g. can you find these forums somehow - if that is the case you should do something like

能否以务实的方式制定这些论坛 ID？例如，你能以某种方式找到这些论坛吗——如果是这样的话，你应该做一些类似的事情

Topic.all(:joins => "left join forums on (forums.id = topics.forum_id and some_condition)", :conditions => "forums.id is null")

Which would be more efficient than doing an SQL not in

哪个比执行 SQL 更有效 not in