Rails - Oct 2006 - EZ_Where help, field IS NULL

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?

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---

Hi Alex-

On Oct 11, 2006, at 12:14 PM, Alex Wayne wrote:
>
> 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







--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---

Ezra Zygmuntowicz wrote:
> 
> 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
Ah, I was linked to a non rubyforge svn somehow.  Patched up and using 
:null and all is well.

Thanks for the awesome plugin, Ezra.

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---