ActiveRecord’s 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 users table
with 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:
User.where.not(favorite_animal: "snake")
Can you see the bug above? I didn’t initially. What I didn’t realize is that
ActiveRecord’s 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 NULL:
select * from users where favorite_animal IS NULL OR favorite_animal != 'snake'
To achieve this query in Rails, we can use ActiveRecord’s or:
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. Instead
of:
User.where.not(subscribed: true)
to include NULL, just use a regular where:
User.where(subscribed: [nil, false])
The takeaway: When using where.not, be explicit if you want NULL values.
What’s next
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
have where.not match null by default.