Posted about 10 years ago. Visible to the public.

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

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. By preloading associations you can prevent the n+1 query problem 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:

Copy
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:

Copy
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:

Copy
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:

Copy
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:

Copy
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).

makandra has been working exclusively with Ruby on Rails since 2007. Our laser focus on a single technology has made us a leader in this space.

Owner of this card:

Avatar
Henning Koch
Last edit:
almost 8 years ago
Keywords:
ambigous
About this deck:
We are makandra and do test-driven, agile Ruby on Rails software development.
License for source code
Posted by Henning Koch to makandra dev
This website uses short-lived cookies to improve usability.
Accept or learn more