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

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>)

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

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

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.

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

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 then filter out the ids you don't want, you could do this:

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

Rails 4 makes it so much easier!

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.

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

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

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.

ActiveRecord will handle that nicely for you too:

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

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:

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)}

Here is a more complex "not in" query, using a subquery in rails 4 using squeel. Of course very slow compared to the equivalent sql, but hey, it works.

    scope :translations_not_in_english, ->(calmapp_version_id, language_iso_code){
      join_to_cavs_tls_arr(calmapp_version_id).
      joins_to_tl_arr.
      where{ tl1.iso_code == 'en' }.
      where{ cavtl1.calmapp_version_id == my{calmapp_version_id}}.
      where{ dot_key_code << (Translation.
        join_to_cavs_tls_arr(calmapp_version_id).
        joins_to_tl_arr.    
        where{ tl1.iso_code == my{language_iso_code} }.
        select{ "dot_key_code" }.all)}
    }

The first 2 methods in the scope are other scopes which declare the aliases cavtl1 and tl1. << is the not in operator in squeel.

Hope this helps someone.

You can use sql in your conditions:

Topic.find(:all, :conditions => [ "forum_id NOT IN (?)", @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

Piggybacking off of jonnii:

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

using pluck rather than mapping over the elements

found via railsconf 2012 10 things you did not know rails could do

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:

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

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)

If someone want to use two or more conditions, you can do that:

your_array = [1,2,3,4]
your_string = "SOMETHING"

YourModel.where('variable1 NOT IN (?) AND variable2=(?)',Array.wrap(your_array),your_string)

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.

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)