The first public release of the CriteriaQuery plugin is available.

CriteriaQuery is an extension to the ActiveRecord find mechanism. It allows object-oriented construction of queries.

In short, it lets you write:


instead of

Person.find(:all, :conditions=>[' LIKE ? AND LIKE ?', 'name', 'city'], :include=>[:city])



instead of

Person.find(:all, :conditions=>[' LIKE ? AND LIKE ? AND', 'name', 'city', 'state'], :include=>[:city=>[:state]])

This becomes increasingly useful for more complex queries, especially if the queries need to be dynamically constructed based on user input (see the README for examples).

Criteria Queries support joins across multiple associations, as well as using the same table in multiple joins.

Documentation is at:

The plugin is available via svn: .script/plugin install

Cheers, Max

I agree. It looks really useful for a reporting feature I’m about to write. Thanks!



These comments may be partially biased because we are both doing similar work (ARE finders) and CriteriaQuery. They have *alot* in common, but there are some things about CriteriaQuery that I don't get.

Why all of the method chaining? It seems to make the queries almost as long and unreadable as the original.

With the example you gave above I would rather prefer the below since to me it is more readable and there isn't so much .( '..' ). going on.

  Person.find :all,     :conditions=> { :name_like=>'name', :state=>'MI' },     :include => [ :address => [:state] ]

I like what you are thinking with Conjunction and Disjunction, but I am not a big fan of the terminology. I think I would have to read and reread code that looked like:   Person.query.disjunction.first_name_eq('name').last_name_eq('name')

It is shorter then the below, the but the below to me looks easier to read, maybe it is because there is visual separation?   Person.find :all,        :conditions => { :first_name => 'name', :last_name => 'name' }

I may just like the whitespace separation over the use of .( 'arg' ). chained together.

What are your thoughts on long queries? Can method chaining do them elegantly?

The main reasoning behind the notation is to make it very easy to build complex conditional queries easily. The method chaining is not required, it's really just some syntactic sugar. You can also write the above query as

pq = Person.query pq.first_name_eq('name') pq.last_name_eq('last_name') pq.find

For simple queries like this, criteria_query does not offer much of an advantage (although on even the simple queries I looked at, there's about 30% less code to write).

For really complex stuff, you also use the block notation, which is structurally nicer:

pq = Person.query pq.first_name_eq('name') pq.last_name('eq) pq.join('addres') do |address|   address.or do |streets|     address.street_1_like('%street%')     address.street_2_like('%street%')   end   address.city_eq('Sydney')   address.join('country') do |country|     country.name_eq('Australia')   end end

It becomes more useful if the conditions are dependant on user input:

  ...   address.or do |streets|     address.street_1_like(params[:street]) if params[:street]     address.street_2_like(params[:street]) if params[:street]   end   ...

Empty subrestrictions do not generate any sql, so the above is safe.

I do quite like the array notation of conditions, but I can't see how you would model joins and disjunctions easily without messing up the syntax too much - but you may have some ideas about that.

I really like the way that criteria_query handles joins. Have you looked at the naming scheme for join aliases in ActiveRecord? That makes it almost impossible to use multiple joins in a conditional query, as the aliases are based on the order in which joins to the same table appear.

Say Person has two relationships to city, called lives_in and works_in, and you want to find all people based on user input into two search fields:

1. User has entered values for both cities:

Person.find(:all, :conditions=>[' AND works_ins_people=?', 'lives', 'works'], :include=>[:lives_in, :works_in])

1. User has entered values for lives_in:

Person.find(:all, :conditions=>[', 'lives' ], :include=>[:lives_in])

1. User has entered values for works_in:

Person.find(:all, :conditions=>['', 'works'], :include=>[:works_in])

This is an area where the .join() stuff gives a significant advantage.

I can see a potential advantage in combining the two approaches and doing something like this:

pq = Person.query pq.conditions = [ :first_name_eq=>'name', :last_name_eq=>'last_name' ] pq.join('address') do |address|   address.conditions = [ :street_1_like=>'%street%', :street_2_like=>'%street%' ] end

The benefit of having one expression per method call lies in the ability to decide for each statement whether to add it to the query or not. In the combined example, you would have to have some conditional statement that pieces together the conditions array first, which defeats the purpose somewhat.

Interested in hearing your thoughts.

Cheers, Max

is Criteria Query no longer maintained? I tried the link at <; but it gave me a page not found error.