Half-finished painted wall

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.

Instead of…

…expecting where.not to return nil values:

non_oat_options = Coffee.where.not(milk: "Oatly")


…an or clause to explicitly request the nil values.

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 nil value.

non_oat_options_2 = Coffee.where.not(milk: [nil, "Oatly"])

But why?

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.

Why not?

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.