Criteria for ActiveRecord

Get Version


What is it?

Users of Hibernate, Torque (in Java) and Propel (in PHP) will be familiar with the concept of criteria as a method of building, in an object orientated manner, complex queries for the underlying Object Relational Mapping (ORM) framework.


Traditionally, when building queries with ActiveRecord, you would create a hash of :conditions, :select, :include, :limit, etc. This is fine for simple queries, but imagine a situation where one wants to build some basic criteria and pass that on to a series of “filters” that may wish to append more criteria. You will probably find that you make your filter append it’s SQL to the various bits of the query hash. In some cases you may even find that you have to introspect the existing data to find out if some statements are already in there, etc. Criteria aims to solve this by providing a structured, object-orientated approach to building the criteria.


# sudo gem install criteria

Things you need to know

Lets say you have an ActiveRecord class called User with several columns including :email. You gain access to a Criteria::Column object by doing either of the following:
# or

The second method is provided for situations where there is a naming collision with other static methods already defined on the class. This object allows you to specify constraints and expressions:"")
# or == ""

The above are equivalent. The first is provided to be a more compatible and consistent way of defining an equals relationship, but the second is provided because its kinda cool.

A Criteria is a collection of Criterion. A Criterion is an expression of some kind about a class’s attribute, for example: will return a Criterion instance that represents that expression. A Criteria is also a Criterion and so a Criteria object can also be a collection of other Criteria as well as Criterion instances. If you combine a Criterion with any other Criterion or Criteria, a Criteria instance will be returned:

c ="")
# c is_a Criterion

c ="") & User.password.eq("password")
# c is_a Criteria

Using this, you can then chain together several statements:

c ="").and(User.password.eq("password")).and(
# OR
c ="") & User.password.eq("password") & (
# c is_a Criteria

Setting limit & offset

Setting the limit and offset is a simple matter of setting the appropriate attribute on the Criteria instance:

c ="").and(User.password.eq("password")).and(
c.limit = 10
c.offset = 2
c.list				# returns an array of User instances, see below

Setting order_by & group_by

Setting these is slightly more complex, but still pretty simple.

In order to specify a column to sort by, we use a similar notation to that used to express Criterion:

order = User.created_at.asc
# order is_a Criteria::Order

To apply this to a Criteria, we simple append it to the order_by or group_by attributes:

c = User.new_criteria
c.order_by << User.created_at.asc

Just append more order statements to order or group by multiple columns. The order in which these statements are added is the order in which they appear in the SQL clause.

Using the ruby language syntax

If you do use the following notation, you will need to make sure that you nest your queries in () to avoid confusion. e.g.:

# Do this
( == "") & (User.password == "password")
# NOT this == "" & User.password == "password"

Also note that the “and” and “or” operator do not work as expected… best to just avoid those for the time being.

You can place these expressions directly inside User.find():

User.find(:first, ( == "") & (User.password == "password"))

Bigger Example

criteria = User.new_criteria
criteria.and do
  User.role.eq(:admin) | |
criteria.and do
  c = User.role.eq(:editor)
  c.or do &

# Find users by User.find()
users = User.find(:all, criteria)
# OR
users = criteria.list

The above query demonstrates how to nest Criteria objects. When a block is passed to and() or or(), it is evaluated and an instance of Criteria or Criterion must be returned. This instance is then added to the current criteria. If we call to_where_sql on the variable criteria, we would get:

    users.role=":admin" OR 
	users.created_at>"2008-04-04 13:55:42"
      users.created_at>"2008-03-15 23:57:04" 
      users.created_at<"2008-04-04 13:55:42"

Or, you could express the above using the terse, ruby syntax:

a = (User.role == :admin) | ( == false) | (User.created_at > 10.hours.ago)
b = (User.role==:editor) | (
b|= ((User.created_at>20.days.ago) & (User.created_at<10.hours.ago))

# Find users using User.find()
users = User.find(:all, a & b)
# OR
users = (a&b).list

Criteria behaviour

When you create a Criteria object, it is either an AND or an OR instance. By default, Criteria will AND together the various criteria. If you then try to OR some criteria, it will create a new Criteria object where the elements will be OR’d together. The current Criteria and your criterion are added to this instance and it is returned, e.g:

c = User.new_criteria

# Using << will append the criterion using AND by default
c << ( == "")
c << (User.password == "password")

# The new value of c will be a Criteria that OR's together the above criteria plus the new criterion
c = c.or(

The above is equivalent to:

c = User.new_criteria
c << ( == "")
c << (User.password == "password")

# Create a new top-level instance that contains the above Criteria plus
c2 = User.new_criteria(:OR)
c2 << c
c2 << (

# Set c to be the new top-level instance
c = c2

Most operations on Criteria and Criterion will result in the top-level instance being returned.


Imagine you have a User, which in turn has many Receipts. You can get the User instance associated with a particular Receipts like this:

r1 = Receipt.find(1)
r2 = Receipt.find(2)
c = User.receipts.contains(r1) & User.receipts.contains(r2)
u = User.find(:first, c)

This works the other way round as well; imagine you have a user instance and you want to find the associated Receipts.

u1 = User.find(1)
u2 = User.find(2)
c = Receipt.user.eq(u1) & Receipt.user.eq(u2).not
receipts = Receipt.find(:all, c)

Associations support is quite basic at the moment. In the above example the association from User to Receipt is called :receipts and from Receipt to User is called :user. I hope to support any associations that ActiveRecord supports in the near future, just need to work out how to coax AR into telling me what they are.

first(), list() and count()

Criteria and Criterion both support the above methods.


This is like User.find(:all, c), but without needing to know that the criteria references User

# Using list() on Criteria
User.new_criteria.and("")).list.each do |u|
  puts "Found #{u}"

# Using list() on Criterion"").list.each do |u|
  puts "Found #{u}"


This is similar to User.find(:first, c)

# Using first()
u = User.new_criteria("")).first
puts "Found #{u}"


This is similar to User.count©

# Using count()
c = User.new_criteria("")).count
puts "There are #{c} results"


Better association support

As mentioned above, the associations support is quite limited, and so more complex relations may well have their SQL rendered incorrectly.

Bugs, Patches and That Sort of Stuff

Please refer to the Criteria project page on

Further documentation

Please see the API Documentation


This code is available under the GPL2 license.

©2008 Wirestorm Ltd & Ray Hilton, 20th June 2008
Theme extended from Paul Battley