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

A picture of me

I am François Beausoleil, a Ruby on Rails and Scala developer. During the day, I work on Seevibes, a platform to measure social interactions related to TV shows. 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