Read more

Do not pass an empty array to ActiveRecord.where when using NOT IN

Andreas Robecke
April 11, 2013Software engineer

Be careful with the Active Record where method. When you accidentally pass an empty array to the where method using NOT IN, you probably will not get what you expected:

User.where("id NOT IN (?)", [])
=>  SELECT `users`.* FROM `users` WHERE (id NOT IN (NULL))
Illustration online protection

Rails Long Term Support

Rails LTS provides security patches for old versions of Ruby on Rails (2.3, 3.2, 4.2 and 5.2)

  • Prevents you from data breaches and liability risks
  • Upgrade at your own pace
  • Works with modern Rubies
Read more Show archive.org snapshot

Even though you might expect this to return all records, this actually results none.

Never use the expression id NOT IN (?) without taking care of this case! See below some workarounds.

Rails < 4

Rails < 4 does not provide a pretty workaround.

ids = []

if ids.present?
  User.where("id NOT IN (?)", ids)
else
  User.all
end

Rails >= 4

If you use the same expression as above in Rails >= 4, it is still broken. But you can use the .not method to work around this issue.

User.where.not(id: []).to_sql 
=> SELECT "users".* FROM "users" WHERE (1=1)

User.where.not(id: [1]).to_sql
=> SELECT "users".* FROM "users" WHERE ("users"."id" != 1)

puts User.where.not(id: [1, 2]).to_sql
=> SELECT "users".* FROM "users" WHERE "users"."id" NOT IN (1, 2)
Posted by Andreas Robecke to makandra dev (2013-04-11 12:04)