CriteriaQuery

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:

Person.query.name_like('name').join('address').city_like('city')

instead of

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

or

Person.query.name_like('name').join('address').city_like('city').join('state').name_eq('state')

instead of

Person.find(:all, :conditions=>['people.name LIKE ? AND addresses.city
LIKE ? AND states.name=?', '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: http://www.muermann.org/ruby/criteria_query

The plugin is available via svn:
.script/plugin install
http://3columns.net/rubyplayground/projects/criteria_query/trunk/criteria_query

Cheers,
Max

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

-Jonathan.

<snip>

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=>['cities.name=? AND
works_ins_people=?', 'lives', 'works'], :include=>[:lives_in,
:works_in])

1. User has entered values for lives_in:

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

1. User has entered values for works_in:

Person.find(:all, :conditions=>['cities.name=?', '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
<http://www.muermann.org/ruby/criteria_query> but it gave me a page
not found error.