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.

Example

Imagine you have an ActiveRecord class User, and it has the following columns/fields: email, password, name, createdAt, role, active. When a user logs in, we want to find the correct user object for the email provided and confirm that the password is correct:

u = User.find_by_email_and_password(email, password)

Now, what if we wanted to then delegate to another method to add some additional criteria? We may end up doing something like this:

where = "email = '#{email}' AND password = '#{password}'"
where = apply_filter(where)
u = User.find(:first, :where => where)

All that icky SQL, not so pretty. Depending on what you need to do, you might find a nicer way around things, but with criteria, we always stay in OO land:

c = User.email.eq(email) & User.password.eq(password)
c = apply_filter(c)
u = User.find(:first, c)

The important thing to notice here is that you can get a Column object by calling ActiveRecord::Base.column_name, e.g. User.email in the above example. This Column object allows you to then express some criteria on it, in the above example we use eq() to mean this column must be equal to the value passed to it. This in turn returns a Criterion object. A Criteria object is a collection of Criterion objects that are AND’d or OR’d depending on how the Criteria was created.

As a further example, imagine we are implementing apply_filer to only allow a user to log in if their ‘role’ field is one of :admin, :user or :editor

def apply_filter(c)
  if apply_fiter?
    c.and User.role.in([:admin, :user, :editor])
  else
    c

end

end

As you can see, appending criteria is pretty easy.

Complex Boolean

The above was quite a simple example, and we only used ANDs and no nested boolean operations.

# We create a Criteria by calling new_criteria on User. This bind the Criteria instance to the User class criteria = User.new_criteria # We call and() and pass it a block. The return criteria.and do User.role.eq(:admin).or(User.active.eq(false)).or(User.created_at.gt(10.hours.ago)) end criteria.and c = User.role.eq(:editor) c|= User.active.eq(true) c.or do User.created_at.gt(20.days.go) & User.created_at.lt(10.hours.ago) end end The above query demonstrates how to nest Criteria objects, using them as if they were Criterion, and thus nest ANDs and ORs. It also shows the various syntactic methods that can be used to achieve the same results. If we call to_where_sql on the 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")))

Chaining

Most methods on Criteria or Criterion return self, so you can chain calls together:

u = User.email.eq(email).and(User.password.eq(password))

Alternative Syntax

Alternatively, rather than express the column constraint like this:

User.createdAt.gt(20.days.ago)

We can use a more natural ruby language approach

User.createdAt > 20.days.ago

Rather than return a boolean true or false, this will return the same Criterion as would be returned in the above statement. When writing lots of criteria, you can see how much nicer this looks

c.and( User.createAt > 20.days.ago )

We can go further and then express AND and OR in a similar way:

(User.createdAt < 10.hours.ago) & (User.createdAt > 20.days.ago)

The above will return a Criteria object populated with the two statements ANDed together.

So, in fact, you could query for the User’s email and password like this:

User.find((User.email == email) & (User.password == password))

Although this looks nicer, it might be removed as it breaks the semantics of the language. Normally you would expect an == or <= to return a boolean value and such comparisons would no longer be possible with these objects.

Joins

ActiveRecord does much of the heavy lifting for us, so joins, etc, are made pretty simple. By virtue of including a criterion for a particular column in a particular table, that table will be added to the list of included tables. It is then delegated to your model hierarchy (via belongs_to, has_many, etc) to determine how these relationships are actually manifest in terms of JOINs in the SQL

More info

Please see the rdoc for more information on the API