How to exclude an array of ids from query in Rails (using ActiveRecord)?

Question

I would like to perform an ActiveRecord query that returns all records except those records that have certain ids. The ids I would like excluded are stored in an array. So:

ids_to_exclude = [1,2,3]
array_without_excluded_ids = Item. ???

I'm not sure how to complete the second line.

Background: What I've already tried:

I'm not sure background is necessary, but I've already tried various combinations of .find and .where. For example:

array_without_excluded_ids = Item.find(:all, :conditions => { "id not IN (?)", ids_to_exclude })
array_without_excluded_ids = Item.where( "items.id not IN ?", ids_to_exclude)

These fail. This tip might be on the right track, but I have not succeeded in adapting it. Any help would be greatly appreciated.

Stinson

Have you tried :conditions => [ "id not IN (?)", ids_to_exclude ]? Note the brackets rather than braces. – Raffinose

In Rails 3.2.1 I used this MyModel.order('created_at DESC').where('id NOT in (?)', ids_to_exclude).limit(5) on a query recently – Pyromania

Answer 1

This should work:

ids_to_exclude = [1,2,3]
items_table = Arel::Table.new(:items)

array_without_excluded_ids = Item.where(items_table[:id].not_in ids_to_exclude)

And it's fully object-oriented with no strings :-)

Pooh

This works like a charm; thank you. I've looked up a bit on Arel; would you have the time to explain exactly what the last two lines do? – Stinson

Arel::Table is an abstraction of your database table with a collection of columns. On my system, I'm using PostgreSQL, so the column is of type ActiveRecord::ConnectionAdapters::PostgreSQLColumn and it represents the characteristics of my DB's column. Each column contains the name of the column, the DB type of that column, defaults, scale, precision characteristics etc. We needed an instance of Table to do our predicate matching against the ID column. The 'not_in' method is of type Arel::Predications and this is directly translated to the SQL that you wanted e.g. "NOT IN (1, 2, 3)". – Pooh

This solution fails for me when the array of ids_to_exclude is empty. I hacked around it with ids_to_exclude.push(-1) if ids_to_exclude.empty? but it made me feel dirty. :( Any thoughts? – Gullett

@Gullett This is because the SQL generated for an empty array would be SELECT "Items".* FROM "items" WHERE "items"."id" NOT IN (NULL). A match against NULL is UNKOWN. It's a three-valued-logic problem: en.wikipedia.org/wiki/Three-valued_logic – Pooh

A bit prettier syntax: Item.where(Item.arel_table[:id].not_in ids_to_exclude) – Herrenvolk

In my case I was using :name and working with STRINGS. Works just as well ;) – Subscribe

@marczking STRINGS? Uhh... I feel sick; going for a lie down. – Pooh

+1 hahaha :). well no way around them in my particular case. xD – Subscribe

This is amazing.. My use case was a weekly Order Export with some crazy data mapping as our accounting has evolved over a long period of time... I really needed my query to return as an active record relation, because I was using a .to_csv class method to map my eCommerce orders into a csv. For some reason it would not work with an array or Order objects, only the active record relation. I couldn't for the life of me figure out how to print our my array of ids, as a comma separated list, in my .where active record SQL query. THANK Scott Lowe ^_^ – Trochal

Answer 2

Rails 4 solution:

ids_to_exclude = [1,2,3]
array_without_excluded_ids = Item.where.not(id: ids_to_exclude)

Yi

This is by far the best option now. – Vernice

Answer 3

You can also use Squeel gem to accomplish such query. Documentation of it, goes here

Omphale

Answer 4

As nslocum wrote, the following works well:

Item.where.not(id: ids_to_exclude)

If your "ids to exclude" come from a query (here with an example condition), you can even take it a step further:

Item.where.not(id: Item.where(condition: true))

This is useful if you need to filter another model:

OtherModel.where.not(item_id: Item.where(condition: true))

Melise