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

ruby-on-rails rails-activerecord
Toby Joiner picture Toby Joiner · Nov 29, 2010 · Viewed 137k times · Source

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.

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.

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

Answer

Jos&#233; Castro picture José Castro · Jul 25, 2011

Rails 4+:

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

Rails 3:

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

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

Related questions

How do you delete an ActiveRecord object?

How do you delete an ActiveRecord object? I looked at Active Record Querying and it does not have anything on deleting that I can see. Delete by id, Delete the current object like: user.remove, Can you delete based on …

Read More
Rails 4: List of available datatypes

Where can I find a list of data types that can be used in Ruby on Rails 4? Such as text string integer float date I keep learning about new ones and I'd love to have a list I could easily …

Read More
Add a reference column migration in Rails 4

A user has many uploads. I want to add a column to the uploads table that references the user. What should the migration look like? Here is what I have. I'm not sure if I should use (1) :user_id, :int …

Read More