Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
69
Help us understand the problem. What is going on with this article?
@blueplanet

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

More than 5 years have passed since last update.

きっかけ

モデルの関連階層が多い場合、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>

状況確認

  • 適当にデータを入れる
  • 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'

user_projects.png
project_tasks.png
task_tags.png
task_category.png

  • 残念な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)
69
Help us understand the problem. What is going on with this article?
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
blueplanet
ブロックチェーンエンジニア

Comments

No comments
Sign up for free and join this conversation.
Sign Up
If you already have a Qiita account Login
69
Help us understand the problem. What is going on with this article?