Why preloading associations "randomly" uses joined tables or multiple queries

Updated . Posted . Visible to the public.

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:

  1. Execute one query per involved table with a condition like ... WHERE id IN (123, 125, 170).
  2. 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).

Henning Koch
Last edit
Michael Leimstädtner
Keywords
ambigous
License
Source code in this card is licensed under the MIT License.
Posted by Henning Koch to makandra dev (2011-04-08 18:44)