# \[ANN\] CriteriaQuery

**URL:** <https://www.ruby-forum.com/t/ann-criteriaquery/67829>\
**Category:** Rails\
**Created:** [September 7, 2006, 2:19am UTC](https://www.ruby-forum.com/t/ann-criteriaquery/67829 "2006-09-07T02:19:25Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![fangheuschrecke](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/58956e/32.png) [@fangheuschrecke](https://www.ruby-forum.com/u/fangheuschrecke)\
**Post date:** [September 7, 2006, 2:19am UTC](https://www.ruby-forum.com/t/ann-criteriaquery/67829/1 "2006-09-07T02:19:25Z")

</div>

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](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](http://3columns.net/rubyplayground/projects/criteria_query/trunk/criteria_query)

Cheers,  
Max

---

<div class="post-metadata">

**Author:** ![fangheuschrecke](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/58956e/32.png) [@fangheuschrecke](https://www.ruby-forum.com/u/fangheuschrecke)\
**Post date:** [September 7, 2006, 6:52am UTC](https://www.ruby-forum.com/t/ann-criteriaquery/67829/2 "2006-09-07T06:52:49Z")

</div>

I think this is totally cool. It will save me hours and hours on a  
directory tool I am writing. Thanks for the thoughtful design.

Paul

Max M. wrote:

> 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])  
> …

---

<div class="post-metadata">

**Author:** ![fangheuschrecke](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/58956e/32.png) [@fangheuschrecke](https://www.ruby-forum.com/u/fangheuschrecke)\
**Post date:** [September 7, 2006, 7:57am UTC](https://www.ruby-forum.com/t/ann-criteriaquery/67829/3 "2006-09-07T07:57:54Z")

</div>

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

-Jonathan.

---

<div class="post-metadata">

**Author:** ![fangheuschrecke](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/58956e/32.png) [@fangheuschrecke](https://www.ruby-forum.com/u/fangheuschrecke)\
**Post date:** [September 7, 2006, 8:51am UTC](https://www.ruby-forum.com/t/ann-criteriaquery/67829/4 "2006-09-07T08:51:59Z")

</div>

-----BEGIN PGP SIGNED MESSAGE-----  
Hash: SHA1

Max M. wrote:

> 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.

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?

I like what you’re thinking, and it is along the same lines that I am  
thinking, although we are approaching from different angles.

At this point I’m just a curious fellow.

Zach D.  
Michigan Ruby U. Group - [http://www.gr-ruby.org](http://www.gr-ruby.org)  
ARE - [http://blogs.mktec.com/zdennis/pages/ARE](http://blogs.mktec.com/zdennis/pages/ARE)

-----BEGIN PGP SIGNATURE-----  
Version: GnuPG v1.4.2.2 (GNU/Linux)  
Comment: Using GnuPG with Mozilla - [http://enigmail.mozdev.org](http://enigmail.mozdev.org)

iD8DBQFE/8B8Myx0fW1d8G0RAu6/AJ9iKEM1rg1oU050MF0So/NG+ia8oQCfXtOn  
efdI2cBAAuzFHbduJgj0jww=  
=ogVY  
-----END PGP SIGNATURE-----

---

<div class="post-metadata">

**Author:** ![fangheuschrecke](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/58956e/32.png) [@fangheuschrecke](https://www.ruby-forum.com/u/fangheuschrecke)\
**Post date:** [September 8, 2006, 12:16am UTC](https://www.ruby-forum.com/t/ann-criteriaquery/67829/5 "2006-09-08T00:16:20Z")

</div>

\> \> 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

---

<div class="post-metadata">

**Author:** ![fangheuschrecke](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/58956e/32.png) [@fangheuschrecke](https://www.ruby-forum.com/u/fangheuschrecke)\
**Post date:** [April 5, 2007, 6:52pm UTC](https://www.ruby-forum.com/t/ann-criteriaquery/67829/6 "2007-04-05T18:52:13Z")

</div>

is Criteria Query no longer maintained? I tried the link at  
[http://www.muermann.org/ruby/criteria\_query](http://www.muermann.org/ruby/criteria_query) but it gave me a page  
not found error.