where.not is a great tool that can make complex queries easier
to read. I’m definitely a fan, but I ran into some behavior that surprised me
the other day and wanted to share what I found.
In my case, which I’ve slightly modified for this post, I had a
favorite_animal column, which could potentially be
NULL. I wanted to
query for all users who didn’t have a strong affinity for snakes, so I put
together the following:
Can you see the bug above? I didn’t initially. What I didn’t realize is that
where.not translates almost directly into a
!= query in the
database. The above fired off this SQL:
select * from users where favorite_animal != 'snake'
In SQL databases,
NULL is treated differently from other values, so
queries never match columns that are set to
NULL. The fix in SQL is to
explicitly also query for
select * from users where favorite_animal IS NULL OR favorite_animal != 'snake'
To achieve this query in Rails, we can use ActiveRecord’s
User.where.not(favorite_animal: "snake") .or(User.where(favorite_animal: nil))
If you are working with a boolean that can be nullable, which is often a bad
choice, it’s a little easier to handle
NULL, just use a regular
User.where(subscribed: [nil, false])
The takeaway: When using
where.not, be explicit if you want
If you found this post helpful, I recommend checking out our post on ActiveRecord’s
where.not or this issue thread in the Rails
GitHub repo which goes into some detail around why the decision was made to not
where.not match null by default.