Explorations...

Today I want to explore how to use Rail’s pagination support when you use find_by_sql, e.g. for complex cases that include joins, custom conditions and piggy-back attributes. The main difficulty is how to get the correct limit-clause into your SQL.

Since version 0.11 Rails has built-in support for pagination, i.e. the technique of splitting a large number of records over several pages. For simple cases this is really simple. If you want to show all records from the persons table with 20 entries per page, for example it gets as easy as adding "paginate :persons, :per_page => 20" to your controller.

However, I had some trouble getting this to work with a more complicated query (that involves joins and piggy-back attributes) and I thought it might be helpful to some people if I share this knowledge and ponder about some improvements.

So, let’s consider an e-mail-account management system. We have three classes EmailAddress, Domain and Account where e-mail-addresses belong to a domain and an account.

class EmailAddress < ActiveRecord::Base
   belongs_to :domain
   belongs_to :account
end

class Domain < ActiveRecord::Base
end

class Account < ActiveRecord::Base
end

As further attributes, e-mail-addresses have a local part and domains have a name.

Now suppose we want to see all e-mail-addresses belonging to an account (could be hundreds) and want to see about 100 addresses per page. We want e-mail-addresses to be presented as local@domain, but retrieving the domain object for every of our (100) e-mail-address objects can be pretty slow, so we want to use the piggy-back approach.

We don’t expect the pagination macro to work in such a complicated case, so our controller will look something like this:

def list
  account = Account.find(@params["id"])
  @address_pages = Paginator.new EmailAddress.count_for_account(account), 
      100, @params['page']
  @addresses = EmailAdress.find_for_account account
end

The count_for_account method is trivial, but this does not work, since the find_for_account should only return the addresses for the current page, so we have to pass this in as well:

@addresses = EmailAdress.find_for_account acount, @address_pages.current

Now the tricky part is how to construct the SQL, so I’ll show this method:

class EmailAddress < ActiveRecord::Base
  def self.find_for_account(account,page=nil)
    sql = "SELECT addresses.*, domains.name AS domain_name " 
    sql << "FROM addresses, domains " 
    sql << "WHERE addresses.domain_id=domains.id " 
    sql << "AND addresses.account_id=? " 
    sql << "ORDER BY domains.name, addresses.local " 
    connection.add_limit!(sql,page.to_sql) if page
    find_by_sql( [sql, account.id ] )
  end
end

(For those unfamiliar with the piggy-back approach: This will return an array of e-mail-adress objects that have an additional attribute domain_name.)

Notice the connection.add_limit line. First of all, it’s great that this is possible in rails! It shows the flexible design of AR in general and the Paginator although I had to read the source to find out how to do it. But once you know how to do it, it’s not so hard after all and that’s why I wrote this post.

So I wondered if there was an easier solution. ActiveRecord’s find_all already has an (undocumented) joins joins attribute, so let’s take a look at the source to see what it does:

def find_all(conditions = nil, orderings = nil, limit = nil, joins = nil)
  sql  = "SELECT * FROM #{table_name} " 
  sql << "#{joins} " if joins
  add_conditions!(sql, conditions)
  sql << "ORDER BY #{orderings} " unless orderings.nil?

  limit = sanitize_sql(limit) if limit.is_a? Array and limit.first.is_a? String
  connection.add_limit!(sql, limit) if limit

  find_by_sql(sql)
end

Great, that’s almost what we want, but the "SELECT * FROM" bothers me, because it means you’ll get into trouble when you are joining tables that have columns with the same name. I believe it would be better if AR was generating SQL that only selects field from the model’s table, plus another optional parameter fields that allows you to add piggy-back fields. If these parameters were also present in Paginator’s paginate method we could even write this in our controller as simple as this without any support in the model.

address_pages, addresses = paginate(
  :email_addresses, 
  :condition => 
    "addresses.domain_id=domains.id AND addresses.account_id=?",
  :order_by => "domains.name, addresses.local",
  :join => ", domains",
  :fields => ["domains.name AS domain_name"],
  :params => [ account.id ]
  )

Okay, still pretty long, so is it worth it?