前提
関連付け：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さんに教えていただきました。
以下の書籍に載っていたとのことです。
