The Buckblog

ActiveRecord’s associations let you specify just about every option that ActiveRecord#find accepts. Want your account’s people returned in sorted order? Just specify ”:order => ‘name’” in the association.

1
2
3

class Account < ActiveRecord::Base
  has_many :people, :order => "name"
end

What could be simpler? Alas, there are all kinds of hidden pitfalls in this approach.

Note that any query scoped by that association is going to use that order, whether you need (or even want) it or not. One consequence of this is that if your scoped find uses an index that doesn’t include the sort key (“name”, in this case), you’re going to be taxing your database unnecessarily. Let’s assume that your “people” table has an index on “account_id” and “name”, so that the default query is nice and performant:

1
2
3
4
5
6
7
8
9
10
11

# SELECT * FROM people WHERE (people.account_id = 1)
# ORDER BY name
account.people

# SELECT * FROM people WHERE (people.account_id = 1
# AND (admin = 1)) ORDER BY name
account.people.find(:all, :conditions => ["admin = ?", true])

# SELECT * FROM people WHERE (people.account_id = 1)
# ORDER BY role, name
account.people.find(:all, :order => "role")

Note that last example, in particular. Specifying a sort order in a scoped query appends to the sort order of the scope. In other words, the query will now work the database harder, unless you just happen to have an index on all three of “account_id”, “role”, and “name”. There is not (currently) an easy way to reach into the parent’s scope and alter (or even “switch off”) the existing order.

This gets even uglier if your association happens to specify a default :include clause:

1
2
3

class Account < ActiveRecord::Base
  has_many :people, :include => :email_addresses
end

Now, if you try and scope the query, the scope will always inherit the requirement that the email_addresses table must be joined in. Now, sometimes you want that join. But all the time? On every scoped query? Are you sure? Not only does this make your database work harder (since queries involving multiple tables require more work than queries against a single table, in general), but it increases the risk of name clashes if you specify conditions or order keys without fully qualifying the table names:

1
2
3
4
5
6
7

# May cause problems if email_address also have a 'role' column
account.people.find(:all, :order => "role")

# The safer way to do it, but you wouldn't know that
# unless you were well acquainted with how the people
# association is defined.
account.people.find(:all, :order => "people.role")

So, what are your options? I would recommend doing like I’ve discussed in other articles, and like Koz and I have pointed out on The Rails Way: either set up a separate association that includes the sorts and includes, or use an extension method. Then, you can use the unadorned version of the association to do your scoped queries safely and efficiently, having a much better idea of what they are going to be doing to your database.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

class Account < ActiveRecord::Base
  # Option #1, using a second association. Allows you to do:
  #   account.people
  #   account.people_by_name
  has_many :people
  has_many :people_by_name, :class_name => "Person", :order => "name"

  # or, using extension methods, you can do:
  #   account.people
  #   account.people.by_name
  has_many :people do
    def by_name
      @by_name ||= find(:all, :order => "name")
    end
  end
end

Either way, you can rest easily knowing that the vanilla “people” association will let you query the database without any scoped assumptions about order. In fact, the only assumption you’ll get at all is the assumption that the query needs to include the “account_id” comparison.

Which is right.