Criteria for ActiveRecord

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.

Synopsis

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.

Installing

# 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:

User.email
# or
User[:email]

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:

User.email.eq("test@example.com")
# or
User.email == "test@example.com"

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: User.active.eq(false) 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 = User.email.eq("test@example.com")
# c is_a Criterion

c = User.email.eq("test@example.com") & User.password.eq("password")
# c is_a Criteria

Using this, you can then chain together several statements:

c = User.email.eq("test@example.com").and(User.password.eq("password")).and(User.active.eq(true))
# OR
c = User.email.eq("test@example.com") & User.password.eq("password") & (User.active.eq(true))
# 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 = User.email.eq("test@example.com").and(User.password.eq("password")).and(User.active.eq(true))
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.email == "test@example.com") & (User.password == "password")
# NOT this
User.email == "test@exmaple.com" & 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.email == "test@example.com") & (User.password == "password"))

Bigger Example

criteria = User.new_criteria
criteria.and do
  User.role.eq(:admin) | User.active.eq(false) | User.created_at.gt(10.hours.ago)
end
criteria.and do
  c = User.role.eq(:editor)
  c|= User.active.eq(true)
  c.or do
	User.created_at.gt(20.days.ago) & User.created_at.lt(10.hours.ago)
  end
end

# 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.active=0 
	OR 
	users.created_at>"2008-04-04 13:55:42"
  ) 
  AND 
  (
    users.role=":editor" 
    OR 
    users.active=1 
    OR 
    (
      users.created_at>"2008-03-15 23:57:04" 
      AND 
      users.created_at<"2008-04-04 13:55:42"
    )
  )
)

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

a = (User.role == :admin) | (User.active == false) | (User.created_at > 10.hours.ago)
b = (User.role==:editor) | (User.active==true)
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 << (User.email == "test@example.com")
c << (User.password == "password")

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

The above is equivalent to:

c = User.new_criteria
c << (User.email == "test@example.com")
c << (User.password == "password")

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

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

Associations

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.

list()

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(User.email.eq("test@example.com")).list.each do |u|
  puts "Found #{u}"
end

# Using list() on Criterion
User.email.eq("test@example.com").list.each do |u|
  puts "Found #{u}"
end

first()

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

# Using first()
u = User.new_criteria(User.email.eq("test@example.com")).first
puts "Found #{u}"

count()

This is similar to User.count©

# Using count()
c = User.new_criteria(User.email.eq("test@example.com")).count
puts "There are #{c} results"

TODO

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

Further documentation

Please see the API Documentation

License

This code is available under the GPL2 license.