ActiveRecord association scoping pitfalls

Posted by Jamis on January 18, 2007 @ 01:23 PM

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.

Posted in Tips & Tricks

Comments

Have something to add? Click here to leave a comment.

18 Jan 2007

1. Dave Hoover said...

Thanks for this flurry of recent posts, Jamis. Nice and brief and packed with goodness!

2. Daniel Morrison said...

When you aren’t fetching an association, you can find yourself passing orders into find() all over your code.

In that case, I’ve found it handy to create a class method on the Model to return the order so that you can call:

Thing.find(:all, :order => Thing.default_order)

That lets you change it in one place.

3. Jamis said...

Daniel, another nice way to handle that is with_scope in a class method:

1
2
3
4
5
6
7
8
9
class Thing < ActiveRecord::Base
  def self.find_with_order(*args)
    with_scope(:find => { :order => "name" }) do
      find(*args)
    end
  end
end

things = Thing.find_with_order(:all, :conditions => "...")

4. Pat Maddox said...

Something to point out to others (I’m pretty sure I learned this from you, Jamis), you can call class methods through the association proxy:

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

class Person < ActiveRecord::Base
  def self.by_name
    find :all, :order => "name"
  end
end

class Account < ActiveRecord::Base
  has_many :people
end

a = Account.find 1
a.people.by_name

This lets any class with a has_many :people declaration get sorted people. You can also use with_scope to allow more arguments if you want.

5. zerohalo said...

More goodness! I must say that of all the Ruby/Rails blogs out there, I learn the most from yours, Jamis. Thanks!

6. Jamis said...

Good point, Pat. Thanks for pointing that out.

And zerohalo, thanks for the kind words!

19 Jan 2007

7. Andreas said...

Very nice :) I use a mixture of the collection-extension and scopes. I have a extra module for most frequently used scopes, usually defined within the model they belong to. Then I can use i.e. with_scope(Scopes::SomeOptions) or from controllers with_scope(Model::Scope::SomeScope). Zerohalo is right. And someone should start a project like the “Rails Documentation Blog Links Protal Thing..” that collects all the links to thos kind of articles. Hehe. Btw. The line-hight of your text is kinda hard on the eyes. Would be killer if you could increase it a notch or two.