activerecord has_many :through find with one sql call

Question

I have a these 3 models:

class User < ActiveRecord::Base
  has_many :permissions, :dependent => :destroy
  has_many :roles, :through => :permissions
end

class Permission < ActiveRecord::Base
  belongs_to :role
  belongs_to :user
end
class Role < ActiveRecord::Base
  has_many :permissions, :dependent => :destroy
  has_many :users, :through => :permissions
end

I want to find a user and it's roles in one sql statement, but I can't seem to achieve this:

The following statement:

user = User.find_by_id(x, :include => :roles)

Gives me the following queries:

  User Load (1.2ms)   SELECT * FROM `users` WHERE (`users`.`id` = 1) LIMIT 1
  Permission Load (0.8ms)   SELECT `permissions`.* FROM `permissions` WHERE (`permissions`.user_id = 1) 
  Role Load (0.8ms)   SELECT * FROM `roles` WHERE (`roles`.`id` IN (2,1)) 

Not exactly ideal. How do I do this so that it does one sql query with joins and loads the user's roles into memory so saying:

user.roles

doesn't issue a new sql query

Answer 1

Loading the Roles in a separate SQL query is actually an optimization called "Optimized Eager Loading".

Role Load (0.8ms)   SELECT * FROM `roles` WHERE (`roles`.`id` IN (2,1))

(It is doing this instead of loading each role separately, the N+1 problem.)

The Rails team found it was usually faster to use an IN query with the associations looked up previously instead of doing a big join.

A join will only happen in this query if you add conditions on one of the other tables. Rails will detect this and do the join.

For example:

User.all(:include => :roles, :conditions => "roles.name = 'Admin'")

See the original ticket, this previous Stack Overflow question, and Fabio Akita's blog post about Optimized Eager Loading.