【Rails】countのN+1問題を解消する

前提

関連付け：users -< products

1
2
3
4
5


class UsersController < ApplicationController
  def index
    @users = User.all
  end
end

1
2
3
4


<% @users.each do |user| %>
  <%= user.display_name %>
  (<%= user.products.count %>)
<% end %>

この場合、user.products.countで毎回クエリが発行されてしまいます。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26


Started GET "/users" for 127.0.0.1 at 2021-11-25 08:47:04 +0900
Processing by UsersController#index as HTML
  Rendering layout layouts/application.html.slim
  Rendering users/index.html.erb within layouts/application
  User Load (2.7ms)  SELECT `users`.* FROM `users`
  ↳ app/views/users/index.html.erb:1
   (5.7ms)  SELECT COUNT(*) FROM `products` WHERE 'products`.`user_id` = 10
  ↳ app/views/users/index.html.erb:3
   (4.5ms)  SELECT COUNT(*) FROM `products` WHERE 'products`.`user_id` = 9
  ↳ app/views/users/index.html.erb:3
   (1.5ms)  SELECT COUNT(*) FROM `products` WHERE 'products`.`user_id` = 8
  ↳ app/views/users/index.html.erb:3
   (2.4ms)  SELECT COUNT(*) FROM `products` WHERE 'products`.`user_id` = 7
  ↳ app/views/users/index.html.erb:3
   (5.8ms)  SELECT COUNT(*) FROM `products` WHERE 'products`.`user_id` = 6
  ↳ app/views/users/index.html.erb:3
   (1.4ms)  SELECT COUNT(*) FROM `products` WHERE 'products`.`user_id` = 5
  ↳ app/views/users/index.html.erb:3
   (14.6ms)  SELECT COUNT(*) FROM `products` WHERE 'products`.`user_id` = 4
  ↳ app/views/users/index.html.erb:3
   (3.3ms)  SELECT COUNT(*) FROM `products` WHERE 'products`.`user_id` = 3
  ↳ app/views/users/index.html.erb:3
   (13.1ms)  SELECT COUNT(*) FROM `products` WHERE 'products`.`user_id` = 2
  ↳ app/views/users/index.html.erb:3
   (16.5ms)  SELECT COUNT(*) FROM `products` WHERE 'products`.`user_id` = 1
  ↳ app/views/users/index.html.erb:3

リファクタリング

`group_of`でハッシュを作る

1
2
3
4
5
6


class UsersController < ApplicationController
  def index
    @users = User.active.with_attached_avatar.order(created_at: :desc)
    @product_count_hash = Product.joins(:users).group('products.user_id').count
  end
end

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


[1] pry(main)> @product_count_hash = Product.joins(:users).group('users.user_id').count
   (0.9ms)  SELECT COUNT(*) AS count_all, `products`.`user_id` AS products_user_id FROM `products` INNER JOIN `users` ON `users`.`product_id` = `products`.`id` GROUP BY `products`.`user_id`
=> {1=>1,
 2=>8,
 3=>1,
 4=>1,
 5=>1,
 6=>1,
 7=>1,
 8=>1,
 9=>1,
 10=>2}

で、{ user_id => product数 }の形式のハッシュを取得できます。
なので、@product_count_hash[user_id]でcountが取得できます。

1
2
3
4


<% @users.each do |user| %>
  <%= user.display_name %>
  (<%= @product_count_hash[user.id] %>)
<% end %>

参考：RailsのN+1対策ガイド
 【Rails】index_byとgroup_byを用いて取り回しのきくハッシュを作成するにちょっと似ている

SQLのselectでカラムを作る

1
2
3
4
5


class UsersController < ApplicationController
  def index
    @users = User.joins(:products).select("users.*, COUNT(products.id) AS count_of_products").group(:id)
  end
end

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20


[1] pry(main)> users = User.joins(:user_products).select("users.*, COUNT(user_products.id) AS count_of_products").group(:id)
  User Load (7.4ms)  SELECT users.*, COUNT(user_products.id) AS count_of_products FROM `users` INNER JOIN `user_products` ON `user_products`.`user_id` = `users`.`id` GROUP BY `users`.`id`
=> [#<User:0x00007fa6b6e99260
  id: 1,
  name: "example1",
  email: "example1@example.com",
  created_at: Sun, 22 Aug 2021 15:31:40.337199000 JST +09:00,
  updated_at: Thu, 23 Sep 2021 23:01:14.072035000 JST +09:00>,
 #<User:0x00007fa6becd0d18
  id: 2,
  name: "example2",
  email: "example2@example.com",
  created_at: Sun, 22 Aug 2021 15:31:40.340775000 JST +09:00,
  updated_at: Thu, 23 Sep 2021 23:01:14.136119000 JST +09:00>
  ...
  ]
[2] pry(main)> users.class
=> User::ActiveRecord_Relation
[3] pry(main)> users.first.count_of_products
=> 1

selectで取得した値はメソッドとして使える。

1
2
3
4


<% @users.each do |user| %>
  <%= user.display_name %>
  (<%= user.count_of_products %>)
<% end %>

@daidai3110さんに教えていただきました。
以下の書籍に載っていたとのことです。

【Rails】countのN+1問題を解消する

前提

リファクタリング

group_ofでハッシュを作る

SQLのselectでカラムを作る

関連記事

目次

`group_of`でハッシュを作る