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."
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, ''].
"" 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.You can use this syntax:
Item.where(context: [nil, ''])
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)}
wheredoes complicate things a bit, but does.blank?()help you at all?Item.where :context.blank?()returns an unfiltered list, andItem.where :context => .blank?()throws a syntax error.@items = Item.where :context => nil || ''won't check for null values, becausenil || ''evaluates to just '', so the resulting query will just beSELECT * FROM items WHERE context = ''