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.

Have you tried `:conditions => [ "id not IN (?)", ids_to_exclude ]`? Note the brackets rather than braces. — mu is too short, Jan 03 '11 at 01:09
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 — Clay, Feb 10 '12 at 18:36

score 37 · Answer 1 · answered Nov 14 '13 at 17:15

37

Rails 4 solution:

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

answered Nov 14 '13 at 17:15

nslocum

5,037
1
27
27

This is by far the best option now. – Aaron Frary Jan 02 '20 at 15:59

score 32 · Accepted Answer · answered Jan 03 '11 at 01:09

32

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

answered Jan 03 '11 at 01:09

Scott

17,127
5
53
64

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? – CuriousYogurt Jan 03 '11 at 03:36
3

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)". – Scott Jan 03 '11 at 10:16
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? – sinisterchipmunk Dec 20 '11 at 00:32
3

@sinisterchipmunk 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: http://en.wikipedia.org/wiki/Three-valued_logic – Scott Dec 20 '11 at 15:02
1

A bit prettier syntax: `Item.where(Item.arel_table[:id].not_in ids_to_exclude)` – vrybas Jul 10 '13 at 11:53
In my case I was using :name and working with STRINGS. Works just as well ;) – MMachinegun May 06 '14 at 16:53
1

@marczking STRINGS? Uhh... I feel sick; going for a lie down. – Scott May 06 '14 at 22:54
+1 hahaha :). well no way around them in my particular case. xD – MMachinegun May 07 '14 at 14:17
1

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 ^_^ – SerKnight Oct 27 '15 at 16:16

score 2 · Answer 3 · answered Feb 19 '13 at 11:03

2

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

answered Feb 19 '13 at 11:03

SriramK89

428
7
17

score 2 · Answer 4 · answered Jun 24 '20 at 07:00

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

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

4 Answers4

Linked