Using Arel with a Rails 2 app

For those new to Arel, make sure you have a read of: http://magicscalingsprinkles.wordpress.com/2010/01/28/why-i-wrote-arel/ and the, albeit slightly out-of-date, documentation at github.

Great, your are back, and you want Arel now.

In fact, one of the first questions I got after my Arel presentation was: can this be used with a Rails 2 app. The answer is quite simple:

Yes/No/Maybe

You see, Arel depends on ActiveSupport version 3.0-pre (soon to be beta). You can not load 2 versions of ActiveSupport into the same app (Ruby Gems will bork), so no.

But… if you dig in to this dependency you discover:

1. It makes use of cattr_accessor once, something that is trivial to rewrite and exists in earlier versions of ActiveSupport
2. It does a bunch of delegations using the delegate helper. Which exists in earlier versions of active support.
3. It uses constantize to find primary keys. (which means it need an inflector which is also in earlier versions of Active Support)

Bottom line is you could theoretically just edit the dependency in the Gemspec and change it to depend on Active Support 2.3.5 with very little ill effect. Which is what I did.

A few minutes of hacking and mucking around with the thorfile and kaboom we have a gem that works against Active Support 2.3.5.

Great, whats the point of that.

The point is that once you can require the arel gem quite a few doors open up.

First things first, you need to require arel and initialize the engine.

# you put this in an initializer (remember to dump it when you upgrade to rails 3)
require 'arel'
Arel::Table.engine = Arel::Sql::Engine.new(ActiveRecord::Base)

Next take this standard AR function

def get_users_by_login(*names)
   User.all(:conditions => ["login in (?)", names]) 
end

get_users_by_login('login1','login2') 
# returns 2 users 

We could rewrite it using Arel:

def get_users_by_login(*names)
  u = Table(:users) 
  User.all(:conditions => u[:login].in(names).to_sql)
end

This result is a bit more verbose, but the key is that we were able to compose our where clause using objects. This gives us much more flexibility. Additionally once you have a lot of sql parameters the ? substitution can become confusing. Eg:

:conditions => ['(age = ? and name = ?) or avatar = ?', 2, 'bob', 'red.png']

What more, Arels composability can help us build some really powerful faceted searches.

def user_scoper(options) 
  # hold a ref to the users table so we can access columns
  # in case no options are sent in just use the table as the scope
  scope = users = Table(:users)
  min_rep, login_filter = options.values_at(:min_reputation, :login_filter) 
  scope = scope.where(users[:min_reputation].gt(min_rep)) if min_rep
  scope = scope.where(users[:login].matches(login_filter) if login_filter 
  scope
end

low_rep_a_users = user_scoper(:min_reputation => 100, :login_filter => 'a%') 
# executes nothing gives us a scope
first_10_low_rep_a_users = low_rep_a_users.take(10) 
# executes nothing another scope 
User.find_by_sql(first_10_low_rep_a_users.to_sql)
# finds the users 

And that just scratches the surface of what we can do. For example:

u = Table[:users]; t = Table[:topics];
# How many users have 0 topics
u.outer_join(t).on(t[:creator_id].eq(u[:id])) \
 .where(t[:id].eq(nil)) \
 .project(u[:id].count) \
 .first.tuple[0].to_i

# runs : 
# SELECT COUNT(`users`.`id`) AS count_id 
#  FROM `users` LEFT OUTER JOIN `topics` ON `topics`.`creator_id` = `users`.`id` 
#  WHERE `topics`.`id` IS NULL

To the best of my knowledge the only way to perform queries like the above in ActiveRecord is by executing a hard coded string. This leaves us with very little flexibility.

So where does this leave us?

I hope the dependency on Active Support is dropped down to 2.3.5 at least. This way we will be able to make use of Arel today, something that will give it some much needed testing and make our lives a bit easier when upgrading.

I think Arel fills a much needed gap when it comes to building dynamic SQL and am sure it will change the way you build db centered apps.

Posted by sam on February 11, 2010

1 comments