Hi Alex-
I am trying to create a conditions caluse with ez_where that looks like:
["enabled = ? AND age_min <= ? AND (age_max >= ? OR age_max IS NULL)", true, range[:max], range[:min]]
Everything is fine except for the last part: "OR age_max IS NULL"
The problem seems to stem that ez_where does not provide a way to use "IS NULL" and "IS NOT NULL". Here is what I tried so far:
conditions = Caboose::EZ::Condition.new do enabled == true age_min <= range[:max] any do age_max >= range[:min] age_max == nil end end
But this leaves that last clause out resulting in: "enabled = ? AND age_min <= ? AND (age_max >= ?)"
I had some minor luck with using append inside the any block:
any do age_max >= range[:min] append 'age_max IS NULL' end
however this generates the appended clause with an AND instead of an OR.
Any tips?
You can't just use nil on the right hand side of an ez_where expression because it will leave out that expression as you discovered. This is so you don't have to say
foo == params[:bar] if params[:bar]
you can leave off the if part and if params[:bar] is nil that statement will be left out of the query. This makes complex queried way easier to write. But it also makes IS NULL a special case so there is a special syntax for that:
conditions = EZ::Where::Condition.new do enabled == true age_min <= range[:max] any do age_max >= range[:min] age_max == :null end end
Also it looks like you are using an old version, please update to the latest here:
svn checkout svn://rubyforge.org/var/svn/ez-where
So for now the way to do IS NULL and IS NOT NULL is liek this:
# IS NULL
foo == :null
# IS NOT NULL
foo! == :null
or append like you used.
-Ezra
