Issue when retrieving records with empty array
I have a table of around 100 Users and I also have an array of user ids. What I wanted to do is show all users who are not a part of this array of user ids. When I do something like this
User.where('id NOT IN (?)', [9, 2, 3, 4])
It successfully returns the records where the user's id does not belong in that array. However if that array is empty like so
User.where('id NOT IN (?)', [])
It does not return any users back and the SQL query looks like this
SELECT "users".* FROM "users" WHERE (id NOT IN (NULL))
Does anyone know why this happens or could this be a bug? I am using Rails 3.2.5 with PostgreSQL.
Answer
In Rails 4 you can use User.where.not(id: []) which will give you the correct result. It produces:
SELECT "users".* FROM "users" WHERE (1 = 1)
Unfortunately User.where('id NOT IN (?)', []) should be equivalent but it is not. It still gives you the wrong result:
SELECT "users".* FROM "users" WHERE (id NOT IN (NULL))
References: