ActiveRecord gives you the :include option to load records and their associations in a fixed number of queries. This is called
preloading or eager loading associations
Show archive.org snapshot
. By preloading associations you can prevent the
n+1 query problem
Show archive.org snapshot
that slows down a many index view.
You might have noticed that using :include randomly seems to do one of the following:
- Execute one query per involved table with a condition like
... WHERE id IN (123, 125, 170). - Execute a single query for a huge table joined from all involved tables.
ActiveRecord prefers option 1, probably because it is less costly. But as soon as your conditions reference another table (like other_table.column), ActiveRecord will go with option 2 and build a joined table.
Read below for more information about this issue. In Rails3+ you can also disable this annoyingly clever behavior.
Why this is bad
This has some develish implications for your deployment, because scopes written to work with option 1 often break with option 2, and you won't notice this during development.
Let's say you have a model Note which belongs to an Author and has a string column category:
class Note < ActiveRecord::Base
belongs_to :author
validates_presence_of :author_id, :category
end
In your controller you provide an action that takes the name of a category from the user input params and loads all the notes in that category:
class NotesController < ApplicationController
def category
@notes = Note.scoped(:conditions => { :category => params[:category] }, :order => 'created_at DESC')
end
end
So far, the code is fine. We will now introduce the bug by preloading the Note#author association:
class NotesController < ApplicationController
def category
@notes = Note.scoped(:conditions => { :category => params[:category] }, :order => 'created_at DESC', :include => :author)
end
end
This controller action works until someone calls the action with a category containing a dot character. This will make ActiveRecord join the notes and authors tables and the query will fail:
Mysql::Error: Column 'created_at' in order clause is ambiguous
Unless you have a test covering this particular case, you won't notice this until after you deployed.
What to do about it
Make sure that when you refer to a column in your :conditions or :order, always qualify the column name with the table name. The controller action above can be fixed by writing it like this:
class NotesController < ApplicationController
def category
@notes = Note.scoped(:conditions => { :category => params[:category] }, :order => 'notes.created_at DESC', :include => :author)
end
end
You can also explictely tell ActiveRecord how to preload associations (either JOINs or separate queries).
