[Ruby-on-rails] How to express a NOT IN query with ActiveRecord/Rails?


FYI, In Rails 4, you can use not syntax:

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

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.



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.

I found this article which 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 IN with that, like:

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

You can use sql in your conditions:

Topic.find(:all, :conditions => [ "forum_id NOT IN (?)", @forums.map(&:id)])

Using Arel:


or, if preferred:


and since rails 4 on:


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 = 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.

When you query a blank array add "<< 0" to the array in the where block so it doesn't return "NULL" and break the query.

Topic.where('id not in (?)',actions << 0)

If actions could be an empty or blank array.

This way optimizes for readability, but it's not as efficient in terms of database queries:

# Retrieve all topics, then use array subtraction to
# find the ones not in our list
Topic.all - @forums.map(&:id)

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

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

Or, if using Rails 3+:

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

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

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.

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