A Single Programmer's Blog

by François Beausoleil

• Projects I work on:
• Piston
• XLsuite.com
• XLsuite.org

CriteriaQuery is a Rails plugin which eases arbitrary query building.

From the README:

If you frequently face the problem of constructing complex dynamic queries, you will get some benefit out of this. Consider the following search form (taken from a real application):

Name (first or last): [               ]

Customer Category:    [ Please Select ][^]

Last call between:    [         ] and [         ]

Active Contacts only: [x]

Address

State:                [               ]

City:                 [               ]          

Street Addres:        [               ]      

…

CriteriaQuery allows you to write the above as:

 1 pq = Person.query
 2 
 3 pq.disjunction.first_name_eq(params[:name]).last_name_eq(params[:name]) if params[:name]
 4   pq.category_id_eq(params[:category]) if params[:category]
 5   ...
 6   address = pq.join("address")
 7   address.state_eq(params[:address[:state]]) if params[:address[:state]]
 8   ...
 9 end







 1 pq = Person.query

 2 

 3 pq.disjunction.first_name_eq(params[:name]).last_name_eq(params[:name]) if params[:name]

 4   pq.category_id_eq(params[:category]) if params[:category]

 5   …

 6   address = pq.join("address")

 7   address.state_eq(params[:address[:state]]) if params[:address[:state]]

 8   …

 9 end

This is all fine and dandy, except using the current version (r41 from the repository), you cannot add arbitrary conditions, such as LENGTH(name) > 0.

ActiveRecord scopes to the rescue. I recently hit that in an application where we allow the admin to find only non anonymous estimates. Non anonymous is defined as having either a phone or an E-Mail address.

1 conds = 'LENGTH(phone_number) > 0 OR LENGTH(email) > 0' if @filters[:non_anonymous]
2 Estimate.with_scope(:find => {:conditions => conds}) do
3   @estimates = query.find(:order => 'updated_at DESC')
4 end

Of course, you will ask “why in the world aren’t your fields ”sql">NULL to begin with if they don’t contain a value ?" Now, that is a very interesting question which I will now explore with gusto !

Search

Your Host

I am François Beausoleil, a Ruby on Rails coder. During the day, I work on AdGear, an ad distribution platform for publishers. At night, I am interested many things. Read my biography.

Top Tags

Books I read and recommend

Links

Projects I work on

Projects I worked on