RailsアプリケーションにてN+1問題を解消したいです。

Question

前提・実現したいこと

RailsアプリケーションにてN+1問題を解消したいです。
簡単な記事投稿サイトを作成いたしまして、index.html.erbにてN+1問題がおきています。
いいね機能（likes）、画像表示機能(refile)、acts-as-taggable-onを用いたタグ機能を実装しています。

試したこと

posts.controller.rbのindexアクションを以下に修正しました。

@posts = Post.includes(:post_images, :tags, :likes).page(params[:page]).order(created_at: :desc)

修正を加えたことによって、post_images と acts-as-taggable-on のN+1問題は解消されましたが、likesのN+1問題が解消されません。

発生している問題・エラーメッセージ

以下のようにlikesのSQLが複数回発行されています。

Started GET "/posts" for 172.18.0.1 at 2021-01-14 07:48:55 +0000
Cannot render console from 172.18.0.1! Allowed networks: 127.0.0.1, ::1, 127.0.0.0/127.255.255.255
Processing by Users::PostsController#index as HTML
  Rendering users/posts/index.html.erb within layouts/application
  ActsAsTaggableOn::Tag Load (0.7ms)  SELECT  `tags`.* FROM `tags` ORDER BY taggings_count desc LIMIT 30
  ↳ app/views/users/posts/index.html.erb:13
  Category Load (0.6ms)  SELECT `categories`.* FROM `categories`
  ↳ app/views/users/posts/index.html.erb:40
  CACHE Category Load (0.0ms)  SELECT `categories`.* FROM `categories`
  ↳ app/views/users/posts/index.html.erb:69
  Post Load (0.7ms)  SELECT  `posts`.* FROM `posts` ORDER BY `posts`.`created_at` DESC LIMIT 9 OFFSET 0
  ↳ app/views/users/posts/index.html.erb:87
  PostImage Load (0.8ms)  SELECT `post_images`.* FROM `post_images` WHERE `post_images`.`post_id` IN (11, 10, 9, 8, 7, 6, 5, 4, 3)
  ↳ app/views/users/posts/index.html.erb:87
  ActsAsTaggableOn::Tagging Load (0.9ms)  SELECT `taggings`.* FROM `taggings` WHERE `taggings`.`taggable_type` = 'Post' AND `taggings`.`context` = 'tags' AND `taggings`.`taggable_id` IN (11, 10, 9, 8, 7, 6, 5, 4, 3)
  ↳ app/views/users/posts/index.html.erb:87
  ActsAsTaggableOn::Tag Load (0.8ms)  SELECT `tags`.* FROM `tags` WHERE `tags`.`id` IN (3, 4, 5, 1, 2, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15)
  ↳ app/views/users/posts/index.html.erb:87
  Like Load (0.7ms)  SELECT `likes`.* FROM `likes` WHERE `likes`.`post_id` IN (11, 10, 9, 8, 7, 6, 5, 4, 3)
  ↳ app/views/users/posts/index.html.erb:87
  User Load (0.6ms)  SELECT  `users`.* FROM `users` WHERE `users`.`id` = 1 ORDER BY `users`.`id` ASC LIMIT 1
  ↳ app/views/users/posts/_like.html.erb:1

**⇩ここからN+１問題**

  Like Exists (0.7ms)  SELECT  1 AS one FROM `likes` WHERE `likes`.`post_id` = 11 AND `likes`.`user_id` = 1 LIMIT 1
  ↳ app/models/post.rb:18
   (0.7ms)  SELECT COUNT(*) FROM `likes` WHERE `likes`.`post_id` = 11
  ↳ app/views/users/posts/_like.html.erb:12
  Rendered users/posts/_like.html.erb (38.7ms)
  Like Exists (0.5ms)  SELECT  1 AS one FROM `likes` WHERE `likes`.`post_id` = 10 AND `likes`.`user_id` = 1 LIMIT 1
  ↳ app/models/post.rb:18
   (0.9ms)  SELECT COUNT(*) FROM `likes` WHERE `likes`.`post_id` = 10
  ↳ app/views/users/posts/_like.html.erb:12
  Rendered users/posts/_like.html.erb (9.0ms)
  Like Exists (0.7ms)  SELECT  1 AS one FROM `likes` WHERE `likes`.`post_id` = 9 AND `likes`.`user_id` = 1 LIMIT 1
  ↳ app/models/post.rb:18
   (0.5ms)  SELECT COUNT(*) FROM `likes` WHERE `likes`.`post_id` = 9
  ↳ app/views/users/posts/_like.html.erb:12
  Rendered users/posts/_like.html.erb (8.9ms)
  Like Exists (0.6ms)  SELECT  1 AS one FROM `likes` WHERE `likes`.`post_id` = 8 AND `likes`.`user_id` = 1 LIMIT 1
  ↳ app/models/post.rb:18
   (0.5ms)  SELECT COUNT(*) FROM `likes` WHERE `likes`.`post_id` = 8
  ↳ app/views/users/posts/_like.html.erb:12
  Rendered users/posts/_like.html.erb (6.6ms)
  Like Exists (0.6ms)  SELECT  1 AS one FROM `likes` WHERE `likes`.`post_id` = 7 AND `likes`.`user_id` = 1 LIMIT 1
  ↳ app/models/post.rb:18
   (0.4ms)  SELECT COUNT(*) FROM `likes` WHERE `likes`.`post_id` = 7
  ↳ app/views/users/posts/_like.html.erb:12
  Rendered users/posts/_like.html.erb (6.6ms)
  Like Exists (0.6ms)  SELECT  1 AS one FROM `likes` WHERE `likes`.`post_id` = 6 AND `likes`.`user_id` = 1 LIMIT 1
  ↳ app/models/post.rb:18
   (0.5ms)  SELECT COUNT(*) FROM `likes` WHERE `likes`.`post_id` = 6
  ↳ app/views/users/posts/_like.html.erb:12
  Rendered users/posts/_like.html.erb (6.4ms)
  Like Exists (0.6ms)  SELECT  1 AS one FROM `likes` WHERE `likes`.`post_id` = 5 AND `likes`.`user_id` = 1 LIMIT 1
  ↳ app/models/post.rb:18
   (0.5ms)  SELECT COUNT(*) FROM `likes` WHERE `likes`.`post_id` = 5
  ↳ app/views/users/posts/_like.html.erb:12
  Rendered users/posts/_like.html.erb (8.7ms)
  Like Exists (0.5ms)  SELECT  1 AS one FROM `likes` WHERE `likes`.`post_id` = 4 AND `likes`.`user_id` = 1 LIMIT 1
  ↳ app/models/post.rb:18
   (0.4ms)  SELECT COUNT(*) FROM `likes` WHERE `likes`.`post_id` = 4
  ↳ app/views/users/posts/_like.html.erb:12
  Rendered users/posts/_like.html.erb (7.0ms)
  Like Exists (0.6ms)  SELECT  1 AS one FROM `likes` WHERE `likes`.`post_id` = 3 AND `likes`.`user_id` = 1 LIMIT 1
  ↳ app/models/post.rb:18
   (0.5ms)  SELECT COUNT(*) FROM `likes` WHERE `likes`.`post_id` = 3
  ↳ app/views/users/posts/_like.html.erb:12
  Rendered users/posts/_like.html.erb (7.8ms)

**⇧ここまでN+1問題**

   (0.5ms)  SELECT COUNT(*) FROM `posts`
  ↳ app/views/users/posts/index.html.erb:113
  Rendered users/posts/index.html.erb within layouts/application (542.9ms)
Completed 200 OK in 969ms (Views: 908.1ms | ActiveRecord: 22.8ms)

ソースコード

• index.html.erb

<% @posts.each do |post| %>
	<div class="col-md-4 col-sm-6">
		<%= link_to post_path(post) do %>
			<div class="post-item shadow">
				<div class="post-image">
					<%= attachment_image_tag post.post_images[0], :image, :fill, 1050, 630, format: "jpeg", class: "image" %>
				</div>
				<div class="detail">
					<p class="limit2"><%= post.title %></p>
					<div class="limit1">
						<% post.tags.pluck(:name).each do |tag| %>
							<%= link_to tag, search_path(search_tag: @tag_form + "," + tag, keyword: @keyword_form, address: @address_form, category: @category_form, sort: @sort), class: "badge badge-default" %>
						<% end %>
					</div>
					<div class="post-footer" id="like-<%= post.id %>">

						<%= render "users/posts/like", post: post %>

					</div>
				</div>
			</div>
		<% end %>
	</div>
<% end %>

• _like.html.erb

<% if user_signed_in? %>
  <% if post.liked_by?(current_user) %>
    <% like = Like.find_by(user_id: current_user.id, post_id: post.id) %>
    <%= link_to like_path(like), method: :delete, remote: true do %>
      <span class="glyphicon glyphicon-heart" aria-hidden="true" style="color: red;">
    <% end %>
  <% else %>
    <%= link_to post_likes_path(post), method: :post, remote: true do %>
      <span class="glyphicon glyphicon-heart" aria-hidden="true" style="color: gray;">
    <% end %>
  <% end %>
  <span><%= post.likes.count %></span>
  <span style="margin-left: 12px;"><%= post.created_at.to_s(:datetime) %></span>
<% else %>
  <%= link_to new_user_registration_path do %>
    <span class="glyphicon glyphicon-heart" aria-hidden="true" style="color: red;">
  <% end %>
  <span><%= post.likes.count %></span>
  <span style="margin-left: 12px;"><%= post.created_at.to_s(:datetime) %></span>
<% end %>

• liked_by?

def liked_by?(user)
	likes.where(user_id: user.id).exists?
end

補足情報（FW/ツールのバージョンなど）

• バージョン

Ruby 2.5.7
Rails 5.2.4.2
MySQL 5.7

Comments

[maisumakun]

Post#liked_by?はどのように実装されていますか？

[YYkanagawa]

すいません。
ソースコードの一番下に追記いたしました。

Answer 1

liked_by?の中でlikes.whereと改めてクエリを仕掛ける形となっているので、.includesしていてもそれは使われません。

likesをEnumerableとして扱って、likes.any?{ |like| like.user_id == user.id }のように判定しましょう。

Comments

[YYkanagawa]

ありがとうございます！
一つ目のN+1は解消されました。
しかし、それぞれのいいね数を取得する、SELECT COUNT(*) FROM `likes` WHERE `likes`.`post_id` = 10　のN+1が解消されません。
またお力添えいただけないでしょうか？

[maisumakun]

post.likes.countは、必ずCOUNTクエリを行います。.sizeあるいは.lengthにしましょう。

[YYkanagawa]

ありがとうございました！
解決いたしました！