Posted about 6 years ago. Visible to the public. Repeats.

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

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:

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

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

Never use the expression id NOT IN (?) in any scope! See below some workarounds.

Rails < 4

Rails < 4 does not provide a pretty workaround.

Copy
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.

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

makandra has been working exclusively with Ruby on Rails since 2007. Our laser focus on a single technology has made us a leader in this space.

Owner of this card:

Avatar
Andreas Robecke
Last edit:
7 months ago
by Emanuel De
Keywords:
mysql
About this deck:
We are makandra and do test-driven, agile Ruby on Rails software development.
License for source code
Posted by Andreas Robecke to makandra dev
This website uses cookies to improve usability and analyze traffic.
Accept or learn more