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)