モデル関連の階層が2層以上ある場合のincludes設定

きっかけ

モデルの関連階層が多い場合、includesがうまく設定できなかったので、調べてみました

結論

長いので、結論を先に書きます

User has_many Project has_many Task has_many Tagの場合、includesは下記になります。

<%= render User.includes(projects: [tasks: [:tags, :category]]).all %>

たたき台を用意する

rails g scaffold user name
rails g model project name user:belongs_to
rails g model task name project:belongs_to category:belongs_to
rails g model tag name task:belongs_to
rails g model category name
rake db:migrate

class User < ActiveRecord::Base
  has_many :projects
end

class Project < ActiveRecord::Base
  belongs_to :user
  has_many :tasks
end

class Task < ActiveRecord::Base
  belongs_to :project
  belongs_to :category
  has_many :tags
end

class Tag < ActiveRecord::Base
  belongs_to :task
end

class Category < ActiveRecord::Base
end

users/index.html.erb

<ul>
  <%= render User.all %>
</ul>

users/_user.html.erb

<li>
  <%= user.name %>
  <ul>
    <%= render user.projects %>
  </ul>
</li>

projects/_project.html.erb

<li>
  <%= project.name %>
  <ul>
    <%= render project.tasks %>
  </ul>
</li>

tasks/_task.html.erb

<li>
  <%= task.name %><br>
  Category: <%= task.category.try(:name) %><br>
  Tags: <%= task.tags.collect(&:name).join(', ') %>
</li>

• 下記リンクを参照しbulletを導入する
  • Railsライブラリ紹介: N+1問題を検出する「bullet」

状況確認

• 適当にデータを入れる
• rails sでサーバを起動する
• http://localhost:3000/users にアクセスすると、console上にメッセージが表示され、画面で下記のアラートが出てくるはず

user: gyo
localhost:3000http://localhost:3000/users
N+1 Query detected
  User => [:projects]
  Add to your finder: :include => [:projects]
N+1 Query method call stack
  /Users/gyo/test/multi_level_include/app/views/users/_user.html.erb:4:in `_app_views_users__user_html_erb___436787215809601570_70334830513800'
  /Users/gyo/test/multi_level_include/app/views/users/index.html.erb:2:in `_app_views_users_index_html_erb___1780560768163965305_70334830660840'
user: gyo
localhost:3000http://localhost:3000/users
N+1 Query detected
  Project => [:tasks]
  Add to your finder: :include => [:tasks]
N+1 Query method call stack
  /Users/gyo/test/multi_level_include/app/views/projects/_project.html.erb:4:in `_app_views_projects__project_html_erb__760140089216941139_70334830446180'
  /Users/gyo/test/multi_level_include/app/views/users/_user.html.erb:4:in `_app_views_users__user_html_erb___436787215809601570_70334830513800'
  /Users/gyo/test/multi_level_include/app/views/users/index.html.erb:2:in `_app_views_users_index_html_erb___1780560768163965305_70334830660840'
user: gyo
localhost:3000http://localhost:3000/users
N+1 Query detected
  Task => [:category]
  Add to your finder: :include => [:category]
N+1 Query method call stack
  /Users/gyo/test/multi_level_include/app/views/tasks/_task.html.erb:3:in `_app_views_tasks__task_html_erb__3289746241671965391_70334829873900'
  /Users/gyo/test/multi_level_include/app/views/projects/_project.html.erb:4:in `_app_views_projects__project_html_erb__760140089216941139_70334830446180'
  /Users/gyo/test/multi_level_include/app/views/users/_user.html.erb:4:in `_app_views_users__user_html_erb___436787215809601570_70334830513800'
  /Users/gyo/test/multi_level_include/app/views/users/index.html.erb:2:in `_app_views_users_index_html_erb___1780560768163965305_70334830660840'
user: gyo
localhost:3000http://localhost:3000/users
N+1 Query detected
  Task => [:tags]
  Add to your finder: :include => [:tags]
N+1 Query method call stack
  /Users/gyo/test/multi_level_include/app/views/tasks/_task.html.erb:4:in `_app_views_tasks__task_html_erb__3289746241671965391_70334829873900'
  /Users/gyo/test/multi_level_include/app/views/projects/_project.html.erb:4:in `_app_views_projects__project_html_erb__760140089216941139_70334830446180'
  /Users/gyo/test/multi_level_include/app/views/users/_user.html.erb:4:in `_app_views_users__user_html_erb___436787215809601570_70334830513800'
  /Users/gyo/test/multi_level_include/app/views/users/index.html.erb:2:in `_app_views_users_index_html_erb___1780560768163965305_70334830660840'

• 残念なSQL

Started GET "/users" for 127.0.0.1 at 2014-07-04 18:57:50 +0900
Processing by UsersController#index as HTML
  User Load (0.1ms)  SELECT "users".* FROM "users"
  Project Load (0.2ms)  SELECT "projects".* FROM "projects"  WHERE "projects"."user_id" = ?  [["user_id", 1]]
  Task Load (0.2ms)  SELECT "tasks".* FROM "tasks"  WHERE "tasks"."project_id" = ?  [["project_id", 2]]
  Category Load (0.1ms)  SELECT  "categories".* FROM "categories"  WHERE "categories"."id" = ? LIMIT 1  [["id", 6]]
  Tag Load (0.1ms)  SELECT "tags".* FROM "tags"  WHERE "tags"."task_id" = ?  [["task_id", 11]]
  Category Load (0.1ms)  SELECT  "categories".* FROM "categories"  WHERE "categories"."id" = ? LIMIT 1  [["id", 12]]
  Tag Load (0.1ms)  SELECT "tags".* FROM "tags"  WHERE "tags"."task_id" = ?  [["task_id", 28]]
  Rendered tasks/_task.html.erb (10.5ms)
  Task Load (0.1ms)  SELECT "tasks".* FROM "tasks"  WHERE "tasks"."project_id" = ?  [["project_id", 3]]
  Category Load (0.3ms)  SELECT  "categories".* FROM "categories"  WHERE "categories"."id" = ? LIMIT 1  [["id", 5]]
  Tag Load (0.1ms)  SELECT "tags".* FROM "tags"  WHERE "tags"."task_id" = ?  [["task_id", 38]]
  Rendered tasks/_task.html.erb (3.2ms)
  Task Load (0.1ms)  SELECT "tasks".* FROM "tasks"  WHERE "tasks"."project_id" = ?  [["project_id", 4]]
  Category Load (0.1ms)  SELECT  "categories".* FROM "categories"  WHERE "categories"."id" = ? LIMIT 1  [["id", 11]]
  Tag Load (0.1ms)  SELECT "tags".* FROM "tags"  WHERE "tags"."task_id" = ?  [["task_id", 7]]
  CACHE (0.0ms)  SELECT  "categories".* FROM "categories"  WHERE "categories"."id" = ? LIMIT 1  [["id", 11]]
  Tag Load (0.1ms)  SELECT "tags".* FROM "tags"  WHERE "tags"."task_id" = ?  [["task_id", 9]]
  Category Load (0.1ms)  SELECT  "categories".* FROM "categories"  WHERE "categories"."id" = ? LIMIT 1  [["id", 4]]
  Tag Load (0.0ms)  SELECT "tags".* FROM "tags"  WHERE "tags"."task_id" = ?  [["task_id", 37]]
  Rendered tasks/_task.html.erb (8.2ms)
  Rendered projects/_project.html.erb (30.9ms)
  Project Load (0.1ms)  SELECT "projects".* FROM "projects"  WHERE "projects"."user_id" = ?  [["user_id", 2]]
  Task Load (0.1ms)  SELECT "tasks".* FROM "tasks"  WHERE "tasks"."project_id" = ?  [["project_id", 8]]
......
  Task Load (0.1ms)  SELECT "tasks".* FROM "tasks"  WHERE "tasks"."project_id" = ?  [["project_id", 6]]
  CACHE (0.1ms)  SELECT  "categories".* FROM "categories"  WHERE "categories"."id" = ? LIMIT 1  [["id", 10]]
  Tag Load (0.1ms)  SELECT "tags".* FROM "tags"  WHERE "tags"."task_id" = ?  [["task_id", 3]]
  CACHE (0.0ms)  SELECT  "categories".* FROM "categories"  WHERE "categories"."id" = ? LIMIT 1  [["id", 5]]
  Tag Load (0.1ms)  SELECT "tags".* FROM "tags"  WHERE "tags"."task_id" = ?  [["task_id", 23]]
  Rendered tasks/_task.html.erb (7.3ms)
  Rendered projects/_project.html.erb (11.2ms)
  Rendered users/_user.html.erb (203.6ms)
  Rendered users/index.html.erb within layouts/application (205.4ms)

includesで対応

users/index.html.erb

<ul>
  <%= render User.includes(projects: [tasks: [:tags, :category]]).all %>
</ul>

対応後のSQL

Started GET "/users" for 127.0.0.1 at 2014-07-04 19:01:48 +0900
Processing by UsersController#index as HTML
  User Load (0.1ms)  SELECT "users".* FROM "users"
  Project Load (0.4ms)  SELECT "projects".* FROM "projects"  WHERE "projects"."user_id" IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
  Task Load (0.3ms)  SELECT "tasks".* FROM "tasks"  WHERE "tasks"."project_id" IN (2, 3, 4, 8, 10, 12, 13, 16, 19, 11, 14, 17, 5, 9, 15, 7, 18, 20, 1, 6)
  Tag Load (0.7ms)  SELECT "tags".* FROM "tags"  WHERE "tags"."task_id" IN (11, 28, 38, 7, 9, 37, 27, 3, 23, 15, 34, 5, 17, 18, 20, 31, 13, 25, 4, 14, 22, 24, 10, 19, 1, 26, 30, 16, 35, 29, 32, 2, 33, 6, 12, 21, 36, 39, 40, 8)
  Category Load (0.2ms)  SELECT "categories".* FROM "categories"  WHERE "categories"."id" IN (6, 12, 5, 11, 4, 2, 10, 16, 17, 13, 18, 3, 14, 8, 9, 7, 15)
  Rendered tasks/_task.html.erb (0.6ms)
  Rendered tasks/_task.html.erb (0.4ms)
  Rendered tasks/_task.html.erb (1.0ms)
  Rendered projects/_project.html.erb (4.1ms)
  Rendered tasks/_task.html.erb (2.4ms)
  Rendered tasks/_task.html.erb (1.5ms)
  Rendered collection (0.0ms)
  Rendered projects/_project.html.erb (6.5ms)
  Rendered tasks/_task.html.erb (4.1ms)
  Rendered tasks/_task.html.erb (0.9ms)
  Rendered tasks/_task.html.erb (1.9ms)
  Rendered projects/_project.html.erb (10.1ms)
  Rendered collection (0.0ms)
  Rendered collection (0.0ms)
  Rendered tasks/_task.html.erb (1.1ms)
  Rendered tasks/_task.html.erb (0.6ms)
  Rendered projects/_project.html.erb (3.2ms)
  Rendered collection (0.0ms)
  Rendered collection (0.0ms)
  Rendered tasks/_task.html.erb (0.4ms)
  Rendered tasks/_task.html.erb (0.7ms)
  Rendered collection (0.0ms)
  Rendered projects/_project.html.erb (2.6ms)
  Rendered tasks/_task.html.erb (0.7ms)
  Rendered tasks/_task.html.erb (0.6ms)
  Rendered tasks/_task.html.erb (0.3ms)
  Rendered projects/_project.html.erb (4.1ms)
  Rendered collection (0.0ms)
  Rendered tasks/_task.html.erb (0.6ms)
  Rendered projects/_project.html.erb (1.6ms)
  Rendered users/_user.html.erb (38.8ms)
  Rendered users/index.html.erb within layouts/application (59.0ms)
Completed 200 OK in 64ms (Views: 61.5ms | ActiveRecord: 1.7ms)

実行効率比較

// 対応前
Rendered users/index.html.erb within layouts/application (205.4ms)
// 対応後
Rendered users/index.html.erb within layouts/application (59.0ms)