image by Samuel Zeller (unsplash oBb-Y26PJgg)
Be Aware of nil values when using where.not()
The use of
where.not when building Active Record scopes can help you to elegantly specify scopes for cases where an attribute does not have a specific value.
However the abstraction has some quirks if the attribute you’re querying can be
NULL in your database (
nil in Ruby). The
where.not scope doesn’t automatically return
nil values unless you specify that you want them.
where.not to return
non_oat_options = Coffee.where.not(milk: "Oatly")
or clause to explicitly request the
non_oat_options = Coffee.where.not(milk: "Oatly").or(Coffee.where(milk: nil))
…alternatively you can pass an array of options to the (negated)
where scope that includes a
non_oat_options_2 = Coffee.where.not(milk: [nil, "Oatly"])
Of course this all depends on whether you are expecting the answer to include those nil values!
The positive use,
where(milk: "Oatly"), as expected, will not return
nil values, and you would think that
where.not(milk: "Oatly") would be the complete inverse: but it isn’t.
This is because the SQL result of a
where.not scope is SQL’s
!= operator, which doesn’t return
NULL values from the database.
In some cases this isn’t the logic you want. So in that case… I guess, don’t use it? :-)
Thanks to Youri van der Lans for pointing out the alternative array-based syntax.
Last updated on April 28th, 2019 by @andycroll
An email newsletter, with one Ruby/Rails technique delivered with a ‘why?’ and a ‘how?’ every two weeks. It’s deliberately brief, focussed & opinionated.