Can I write this query more elegantly with AREL/ActiveRecord?

Question

Can I write this query shorter and/or more elegantly using the AREL/ActiveRecord API?

Foo.where("(bar is not null and bar != '') or (baz is not null and baz !='')")

Answer 1

You can do an OR operator with Arel directly but the syntax isn't hugely pretty and can get a bit hard to read. Here is what it would look like in arel:

foo  = Foo.arel_table
Foo.where(foo[:bar].not_eq(nil).and(foo[:bar].not_eq('')).or(foo[:baz].not_eq(nil).and(foo[:baz].not_eq(''))))

I'd suggest looking at the squeel gem. It gives you access to more arel functionality in active record. Your query would like like:

Foo.where{(bar.not_eq nil) & (bar.not_eq '') | (baz.not_eq nil) & (baz.not_eq nil)}

Here is the link for more info: http://erniemiller.org/projects/squeel/

There are a few different ways you can write that in squeel, it supports a few different syntax styles so if you don't liked the one above there are alternatives.

Answer 2

The sane way to deal with this (if you have the rights do do this of course on the database) is to disallow empty string values in the database. Then you can do Foo.where(:bar => nil). I use attribute_normalizer for this, and also to format values correctly.

https://github.com/mdeering/attribute_normalizer

A quick search also reveals nilify blanks, but I haven't tried that because attribute normalizer already provides that functionality for me.

https://github.com/rubiety/nilify_blanks

The resulting sql for the search you want if you have a well kept database (either null or empty string values for empty fields is probably as short as it gets. (I prefer empty strings myself).