Rails Eager Loading CanCan Roles

Question

I am trying to speed up my app by eager loading some sql calls. I am using the CanCan gem to handle my admin authorization. I have a Roles table with three different roles and a many-to-many table roles_users. Each time a page loads with a CanCan ability set it does three separate sql queries.

 Role Load (0.6ms)  SELECT "roles".* FROM "roles" INNER JOIN "roles_users" ON "roles"."id"    
= "roles_users"."role_id" WHERE "roles_users"."user_id" = 2 AND "roles"."name" = 'admin'  
  LIMIT 1
Role Load (0.4ms)  SELECT "roles".* FROM "roles" INNER JOIN "roles_users" ON "roles"."id" 
  = "roles_users"."role_id" WHERE "roles_users"."user_id" = 2 AND "roles"."name" =  
 'manager' LIMIT 1
 Role Load (0.3ms)  SELECT "roles".* FROM "roles" INNER JOIN "roles_users" ON "roles"."id" 
  = "roles_users"."role_id" WHERE "roles_users"."user_id" = 2 AND "roles"."name" = 'user' 
  LIMIT 1

I have tried putting a default_scope :include => :roles in the User class and also putting :includes in the has_and_belongs_to_many calls.

Where can I eager load the Roles table to use only 1 SQL query?

Welcome to stack overflow. Remember to upvote all useful answers, including those to others' questions. Remember to check/approve the best answer to your own questions. — Larry K

David Nguyen David Nguyen · Accepted Answer · 2012-04-25T15:41:04

Looks like you might have some code that looks like the below. Namely, it takes a string or symbol that you need to compare to some value in a role record, and is not the role itself. So look for something like this:

def do_i_have_role(role_name_to_check)
  self.roles.detect do |role|
    role.name == role_name_to_check
  end
end

You could do some eager loading here to fix it up, but another way to skin this cat is to restructure your query. Namely, look for the role object first, then see if your account has that role.

def do_i_have_role(role_name_to_check)
  role = Role.where(:name => role_name_to_check)
  self.roles.include? role
end

Now it's just one hit to the roles database instead of 3 (and a hit to the accounts table and one more hit to the account_user table which you will probably never be able to avoid).

Rails Eager Loading CanCan Roles

2 Answers