Active Record NOT IN queries and NULL with MySQL

Question

I have a query like:

Tag.where('id not IN (?)', current_user.tags.pluck(:id)).uniq

When

current_user.tags.pluck(:id)).uniq

returns NULL, I get no results from the Tag query which isn't the desired behavior.

What am I doing wrong here?

Thanks.

Answer 1

I don't think current_user.tags.pluck(:id) is returning you a nil, it is returning an empty array. ActiveRecord will treat an empty array as a NULL in that context. The result is some nonsensical SQL like this:

select tags.* from tags where id in (null)

Due to the idiosyncrasies of SQL's NULL (in particular x = NULL and x != NULL are both false for all x), an in (null) or not in (null) in a WHERE clause won't match anything.

Rails converting Ruby's [] to NULL is pretty stupid (more discussion on that over here) but even if it was smart enough to raise an exception, you'd still have to deal with the "empty array" case manually with something like this:

tag_ids = current_user.tags.pluck(:id)
if(tag_ids.empty?)
  tags = Tag.all
else
  tags = Tag.where('id not in (?)', tag_ids)
end

And you don't need the uniq in there, the SQL in operator will treat its RHS as a set so duplicates will be collapsed behind the scenes.

Answer 2

Use where.not to let Rails handle when the passed argument is an empty Array.

As mu is too short pointed out, when the passed value to the query condition is an empty Array, ActiveRecord turns that into NULL which messes up your query and will always return an empty result.

The old way of dealing with this is by conditionally check for an empty Array as the argument and not adding this condition. However, with Rails 4 that introduced where.not, we no longer have to have this check.

We can simply do:

Tag.where.not( id: current_user.tags.pluck(:id) ).uniq

And now ActiveRecord will automatically check for an empty Array and when it sees it, the condition becomes 1=1, which essentially is meaningless but more importantly, it just gets ignored and the rest of your query will run as if that condition was never added to the query.