image by Samuel Zeller (unsplash oBb-Y26PJgg)
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.