ActiveRecord: Query where column NOT in array

Question

ActiveRecord has a very neat syntax for querying records where a column is equal to any value in a given array:

For a simple example, let's say you have 10 products with ids 1,2,3,4,5,6,7,8,9,10.

Product.where(id: [2,3,4,5,6])

will return products 2,3,4,5 and 6.

Is there an ActiveRecord equivalent for querying products where the column does not equal any value in an array?

Something like:

Product.where('id != ?', [2,3,4,5,6])

except that it actually works...

And when you pass it [2,3,4,5,6] in this case, it will return products 1,7,8,9 and 10.

EDIT

I need a Rails 3 solution!!!

Patrick Oscity · Accepted Answer · 2014-10-16 09:07:42Z

28

You can negate any where clause with where.not in Rails 4:

Product.where.not(id: [2, 3, 4, 5, 6])

In Rails 3 you can leverage ARel:

Product.where(Product.arel_table[:id].not_in([2, 3, 4, 5, 6]))

The generated SQL in both cases is

SELECT "products".* FROM "products" WHERE ("products"."id" NOT IN (2, 3, 4, 5, 6))

answered Oct 16, 2014 at 8:59

Patrick Oscity

54.9k19 gold badges149 silver badges173 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

That is introduced in Rails 4 right? I am still (reluctantly) using Rails 3! Can you tell me what SQL this query generates?

You can see what sql is generated by calling to_sql on an ActiveRecord relation object. See my edit.

Joshua Pinter · Accepted Answer · 2016-03-09 14:49:41Z

7

Use following for rails 3

Product.where('id NOT IN (?)', [2,3,4,5,6])

Joshua Pinter

47.9k23 gold badges261 silver badges258 bronze badges

answered Oct 16, 2014 at 9:45

Esha

1211 bronze badge