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


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

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

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

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

```

## たたき台を用意する

```bash
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
```

```ruby
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」](http://www.techscore.com/blog/2012/12/25/rails%E3%83%A9%E3%82%A4%E3%83%96%E3%83%A9%E3%83%AA%E7%B4%B9%E4%BB%8B-n1%E5%95%8F%E9%A1%8C%E3%82%92%E6%A4%9C%E5%87%BA%E3%81%99%E3%82%8B%E3%80%8Cbullet%E3%80%8D/)

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

```bash
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](https://qiita-image-store.s3.amazonaws.com/0/4777/41a5959f-a90d-bad4-f239-adb596cb24ad.png "user_projects.png")
![project_tasks.png](https://qiita-image-store.s3.amazonaws.com/0/4777/1259849c-a4e5-071b-c107-9dcd1fcd77c9.png "project_tasks.png")
![task_tags.png](https://qiita-image-store.s3.amazonaws.com/0/4777/88fb72a3-33fc-590d-2949-8ae3aae12e0b.png "task_tags.png")
![task_category.png](https://qiita-image-store.s3.amazonaws.com/0/4777/2603eeaf-d05f-a38f-6c63-64012c1174fc.png "task_category.png")

- 残念なSQL

```base
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

```base
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)
```