by Matt Polito on July 26, 2018

Sometimes you come across a query that looks for an attribute that is NULL 'OR' an empty string. Sure there are options we could employ that may remove the necessity of checking for both but let's see how we can improve our code a bit first!

The majority of the time I see this as a SQL fragment in ActiveRecord:

Foo.where("name is null or name = ''")

Which produces the following SQL:

SELECT * FROM "foos" WHERE (name is null or name = '')

Sure you can say now that ActiveRecord has or it could be done like so:

Foo.where(name: nil).or(name: '')

However, I'm currently in a project that doesn't support or yet so I went playing to see what I could come up with that wouldn't be a SQL fragment. My first thought was to pass a two element array, and hopefully, PostgreSQL would respond the way I wanted with an IN query:

SELECT * FROM "foos" WHERE "foos"."name" IN (NULL, '')

via:

Foo.where(name: [nil, ''])

With testing, I found that the IN query above would find rows where name is an empty string but not NULL. Much to my surprise, ActiveRecord DID do what I wanted but not how I expected. Somewhere, I have yet to find the code; there must be a special case when [nil, ''] is given, the query we wanted gets produced:

SELECT * FROM "foos" WHERE ("foos"."name" IS NULL OR "foos"."name" = '')

Now that we're back in ActiveRecord land we can utilize not as well (provided you're working with a Rails version that supports it: v4.0.0 and above. It also creates a query we expect:

Foo.where.not(name: [nil, ''])

produces:

SELECT * FROM "foos" WHERE (NOT (("foos"."name" IS NULL OR "foos"."name" = '')))

With this new found info, I can simplify some code and remove SQL fragments!