Testing for an empty string in an ActiveRecord query

Question

In a Rails app, what is the cleanest way to check if a property is nil or empty using the ActiveRecord where method?

This works, but it seems that there should be a nicer, built-in way to do it:

@items = Item.where :context => nil || ''

It's hard to do much with Google-fu when your search includes terms like "where."

Your `where` does complicate things a bit, but does [`.blank?()`](http://api.rubyonrails.org/classes/Object.html#method-i-blank-3F) help you at all? — sarnold, Nov 13 '11 at 00:51
Where would that go in the code above? `Item.where :context.blank?()` returns an unfiltered list, and `Item.where :context => .blank?()` throws a syntax error. — Josh Earl, Nov 13 '11 at 01:00
`@items = Item.where :context => nil || ''` won't check for null values, because `nil || ''` evaluates to just '', so the resulting query will just be `SELECT * FROM items WHERE context = ''` — stereoscott, Jun 04 '14 at 18:18

score 16 · Answer 1 · edited May 23 '17 at 10:29

What you have wouldn't work.

@items = Item.where context: nil || ''

really evaluates to:

@items = Item.where context: ''

So you wouldn't find items with context set to nil using this method.

Side Note

Using thing || other in this way never works. You cannot write if item == 'test' || 'something' and expect it to work in cases where item is 'something'. It would only work in cases where item is 'test'. To get something like this to work you would need to write if item == 'test' || item == 'something'. This isn't how humans talk, but it is how computers read.

Back to the main event

One way to write a query that would work is:

Item.where("context = ? or context = ?", nil, '')

This reads as: find all Items where context is nil or context is ''.

While this works, it's not considered particularly "Rails-y". A better way would be:

Item.where(context: [nil, ''])

This reads as: find all Items where context is in [nil, ''].

Related question

I wanted to account for both `""` and `''` so I crafted something like `Item.where("context = ? or context = ? or context = ?", nil, '', "")`. Found soon I didn't need both `""` and `''` because they are equal in Ruby's eyes. — Tass, Jun 27 '16 at 19:42

Dty · Accepted Answer · 2017-09-26T15:18:03.237

3

You can use this syntax:

Item.where(context: [nil, ''])

edited Sep 26 '17 at 15:18

answered Nov 13 '11 at 01:25

Dty

12,253
6
43
61

score 0 · Answer 3 · answered Nov 13 '11 at 01:37

I like to avoid this problem by setting a default value for such columns.

If that isn't an option for you then you'll need to use a SQL clause.

@items = Item.where "`items`.`context`='' OR `items`.`context` IS NULL"

I use the Squeel gem (http://erniemiller.org/projects/squeel/) which makes it easy to use OR operators. I recommend checking it out. An example with Squeel would be:

@items = Item.where{context.eq('') | context.eq(nil)}

Testing for an empty string in an ActiveRecord query

3 Answers3