はじめに
今回ポートフォリオ(以後PF)制作で、
日本各地の名所を投稿できるサイトを制作しました。
実際に製作したサイトと、コード(GitHub)は下記のURLからご覧ください。
・サイトURL : https://japansiteinfo.com (今後予告なく公開停止する場合があります。ご了承ください。)
・GitHubのURL : https://github.com/yuta-pharmacy2359/dwc_JapanSiteInfo_app
今回は前回から引き続き「N+1問題」に関して、includesメソッドで解決できないものについて
joinsメソッドおよびgroupメソッドを用いた解決法を紹介したいと思います。
本題
1. 前回のおさらい&今回扱う問題
前回の記事(https://qiita.com/yuta-pharmacy2359/items/cf30a20fbea9347c0b72) では、includesメソッドで「N+1問題」を解決できない以下の6機能のうち、
・(ユーザー詳細画面における)1人のユーザーが獲得した総いいね数表示
・(キーワード一覧画面における)1つのキーワードにおけるスポット評価の平均値表示
・ランキング機能(ユーザー1人ごとの総獲得いいね数)における1人のユーザーの総いいね数表示
・ランキング機能(ユーザー1人ごとの総獲得いいね数)における1人のユーザーの総スポット数表示
・フォロー数、フォロワー数表示
・(フォロー・フォロワー画面における)各ユーザーの最終更新日表示
最上段の「(ユーザー詳細画面における)1人のユーザーが獲得した総いいね数表示」について、joinsメソッドを用いた解決法を紹介しました。
こちらは「基本的にいいねを集計するスポットは当該ユーザーのもののみである(テーブル結合後にグループ分けしたりする必要がない)」ため、spotsテーブルとfavoritesテーブルを結合した後はcountメソッドでそのレコード数を集計すればOKでした。
一方、それ以外に関しては**「一覧画面などでユーザー(またはスポット)全体から必要なデータを適宜抽出して表示する」**必要があるため、単にテーブル結合後にcountメソッドなどを利用するだけでは解決することができません。
参考までに、「1人のユーザーが獲得した総いいね数表示」について、ユーザー詳細画面とランキング(その他一覧系)画面における違いを下図に示しました。
図の通り、ランキング画面では、user_idごとの獲得いいね数を集計する必要があるため、countメソッドの使用前にuser_idごとにグループ分けする必要があります。
(なお、図では比較のため「ランキング画面における1人のユーザーが獲得した総いいね数」を取り上げましたが、ランキング機能については「N+1問題」以外にも要説明事項がいくつかあるので、また別の記事で紹介します。)
そこで今回は、**「(キーワード一覧画面における)1つのキーワードにおけるスポット評価の平均値表示」**を例に、joinsメソッドで複数のテーブルを結合した後にgroupメソッドでテーブル内のデータを仕分けて集計する方法を紹介します。
2. 機能概要と発生した問題点
当PFでは、投稿するスポットに任意のキーワードを付与することができる仕様となっています。
さらに、上図のように、スポット詳細画面に表示されるキーワードはそのキーワード詳細画面のリンクとなっており、そこで同じキーワードを持ったスポットの一覧を見ることができます。
また、キーワード一覧画面には、そのキーワードを持ったスポットの評価の平均値が表示される仕様になっています。
その機能をテーブル同士の繋がりで表したのが下図です。
(データの繋がりが見やすくなるよう、テーブルの内容は上図から変えています。ご了承ください。)
当PFでは、**1つのスポットにつき複数のキーワードを付与することができる仕様(同時に、他のスポットに既出のキーワードを付与可能)であるため、spotsテーブルとkeywordsテーブルは「多対多の関係」**となります。
そのため、それら2つのテーブルの間にkeyword_relationshipsテーブルという中間テーブルを設けています。
そしてキーワード詳細画面では、中間テーブルを介してkeyword_idが一致しているスポットが抽出され表示されます。
(下図の例では、キーワードid=1である「東京タワー」に紐づくスポットとして、id=1,6,9,12のスポットが抽出されます。)
さらにキーワード一覧画面では、その抽出されたスポットの評価の平均値を算出し表示しています。
(下図の例では、(5 + 2 + 4 + 5) / 4 = 4がid=1のキーワードにおけるスポットの評価平均値となります。)
「N+1問題」を考慮しないときのindexのビューファイルおよびコントローラーファイルでの記述は以下の通りとなります。
<% @keywords.each do |keyword| %>
<tr>
(中略)
<td>
<%= keyword.spots.count %>スポット
</td>
<% if keyword.spots.average(:rate).present? %>
<td><%= keyword.spots.average(:rate).round(2) %></td>
<!-- 以下は評価を星マークで表示するためのJavaScriptの記述。詳細は別記事で紹介予定 -->
<script>
評価を星マークで表示するためのJavaScriptの記述。詳細は別記事で紹介予定
if(!$("#star-rate-<%= keyword.id %> img").length) {
$('#star-rate-<%= keyword.id %>').raty({
size: 36,
starOff: '<%= asset_path('star-off.png') %>',
starOn: '<%= asset_path('star-on.png') %>',
starHalf: '<%= asset_path('star-half.png') %>',
readOnly: true,
score: <%= keyword.spots.average(:rate) %>,
});
}
</script>
<% else %>
<td>評価なし</td>
<% end %>
</tr>
<% end %>
def index
# Ransack(検索・ソート機能が利用できるgem)を利用している関係で、以下の記述となっています。
@q = Keyword.ransack(params[:q])
@q.sorts = 'updated_at desc' if @q.sorts.empty?
@keywords = @q.result.page(params[:page])
end
一般的に平均値を表示したい場合は、以下のように記述します。
モデル名.average(:カラム名)
また、その後ろのroundメソッドは、**「表示する数値を小数第(引数)桁までに指定する」**メソッドです。
今回の場合は引数が2ですので小数第2位まで表示することとなります。
そして、スポットの評価は「無評価(nil)」でも許容される設定にしており、もし**キーワードに紐づくスポットが全て「無評価」であった場合は評価の平均値が存在しない(nil)**ことになるため、その場合の分岐としてelse以下で「評価なし」を表示するようにしています。
この記述では、以下のようなアクセスが行われます。
Keyword Load (0.3ms) SELECT “keywords”.* FROM “keywords” ORDER BY “keywords”.”updated_at” DESC LIMIT ? OFFSET ? [[“LIMIT”,10],[“OFFSET”,0]]
KeywordRelationship Load (0.2ms) SELECT "keyword_relationships".* FROM "keyword_relationships"."keyword_id" IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) [["keyword_id", 10], ["keyword_id", 9], ["keyword_id", 8], ["keyword_id", 7], ["keyword_id", 6], ["keyword_id", 5], ["keyword_id", 4], ["keyword_id", 3], ["keyword_id", 2], ["keyword_id", 1]]
Spot Load (0.2ms) SELECT "spots".* FROM "spots" INNER JOIN "keyword_relationships" ON "spots"."id" = "keyword_relationships"."spot_id" WHERE "keyword_relationships"."keyword_id" = ? ORDER BY "spots"."id" DESC LIMIT ? [["keyword_id", 10], ["LIMIT", 1]]
(0.2ms) SELECT COUNT(*) FROM "spots" INNER JOIN "keyword_relationships" ON "spots"."id" = "keyword_relationships"."spot_id" WHERE "keyword_relationships"."keyword_id" = ? [["keyword_id", 10]]
(0.1ms) SELECT AVG("spots"."rate") FROM "spots" INNER JOIN "keyword_relationships" ON "spots"."id" = "keyword_relationships"."spot_id" WHERE "keyword_relationships"."keyword_id" = ? [["keyword_id", 10]]
CACHE (0.0ms) SELECT AVG("spots"."rate") FROM "spots" INNER JOIN "keyword_relationships" ON "spots"."id" = "keyword_relationships"."spot_id" WHERE "keyword_relationships"."keyword_id" = ? [["keyword_id", 10]]
CACHE (0.0ms) SELECT AVG("spots"."rate") FROM "spots" INNER JOIN "keyword_relationships" ON "spots"."id" = "keyword_relationships"."spot_id" WHERE "keyword_relationships"."keyword_id" = ? [["keyword_id", 10]]
Spot Load (0.2ms) SELECT "spots".* FROM "spots" INNER JOIN "keyword_relationships" ON "spots"."id" = "keyword_relationships"."spot_id" WHERE "keyword_relationships"."keyword_id" = ? ORDER BY "spots"."id" DESC LIMIT ? [["keyword_id", 9], ["LIMIT", 1]]
(0.2ms) SELECT COUNT(*) FROM "spots" INNER JOIN "keyword_relationships" ON "spots"."id" = "keyword_relationships"."spot_id" WHERE "keyword_relationships"."keyword_id" = ? [["keyword_id", 9]]
(0.1ms) SELECT AVG("spots"."rate") FROM "spots" INNER JOIN "keyword_relationships" ON "spots"."id" = "keyword_relationships"."spot_id" WHERE "keyword_relationships"."keyword_id" = ? [["keyword_id", 9]]
CACHE (0.0ms) SELECT AVG("spots"."rate") FROM "spots" INNER JOIN "keyword_relationships" ON "spots"."id" = "keyword_relationships"."spot_id" WHERE "keyword_relationships"."keyword_id" = ? [["keyword_id", 9]]
CACHE (0.0ms) SELECT AVG("spots"."rate") FROM "spots" INNER JOIN "keyword_relationships" ON "spots"."id" = "keyword_relationships"."spot_id" WHERE "keyword_relationships"."keyword_id" = ? [["keyword_id", 9]]
Spot Load (0.2ms) SELECT "spots".* FROM "spots" INNER JOIN "keyword_relationships" ON "spots"."id" = "keyword_relationships"."spot_id" WHERE "keyword_relationships"."keyword_id" = ? ORDER BY "spots"."id" DESC LIMIT ? [["keyword_id", 8], ["LIMIT", 1]]
(0.2ms) SELECT COUNT(*) FROM "spots" INNER JOIN "keyword_relationships" ON "spots"."id" = "keyword_relationships"."spot_id" WHERE "keyword_relationships"."keyword_id" = ? [["keyword_id", 8]]
(0.1ms) SELECT AVG("spots"."rate") FROM "spots" INNER JOIN "keyword_relationships" ON "spots"."id" = "keyword_relationships"."spot_id" WHERE "keyword_relationships"."keyword_id" = ? [["keyword_id", 8]]
CACHE (0.0ms) SELECT AVG("spots"."rate") FROM "spots" INNER JOIN "keyword_relationships" ON "spots"."id" = "keyword_relationships"."spot_id" WHERE "keyword_relationships"."keyword_id" = ? [["keyword_id", 8]]
CACHE (0.0ms) SELECT AVG("spots"."rate") FROM "spots" INNER JOIN "keyword_relationships" ON "spots"."id" = "keyword_relationships"."spot_id" WHERE "keyword_relationships"."keyword_id" = ? [["keyword_id", 8]]
・・・
(以下、keyword_id = 1まで上記の繰り返し)
さすがに途中で省略させていただきました(笑)
まず、キーワード一覧画面では**「そのキーワード内の最新のスポットの画像」と「そのキーワードを持つスポット数」**を表示しているため、keywordsテーブルと同時にspotsテーブルにもアクセスが行われることになります。
(「そのキーワードを持つスポット数」については「とある理由」でSpot Loadの真下の行にもう一回アクセスが行われた形跡がありますが、そちらも当記事の最後の方で取り上げていますのでご安心ください。)
さらに、問題のスポット評価平均値表示の部分では一つのキーワードにつき3回ずつアクセスが行われています。その内訳ですが、
indexのviewファイルにおいて、
・<% if keyword.spots.average(:rate).present? %>
・<%= keyword.spots.average(:rate).round(2) %>
・<%= keyword.spots.average(:rate) %>
の部分でそれぞれアクセスが行われています。
さすがに表示内容に対してアクセス回数が多すぎるので、次項で一つずつ解消していきます。
3. 解決法(前半)
まず、spotsテーブルを何度も読み込んでしまう部分に関しては、以前も紹介したincludesメソッドで解決することができます。
keywordのコントローラーファイルにおいて、
def index
@q = Keyword.ransack(params[:q])
@q.sorts = 'updated_at desc' if @q.sorts.empty?
@keywords = @q.result.page(params[:page]).includes(:spots)
end
最後の@keywordsの定義の部分で、末尾にincludes(:spots)を追加すればOKです。
(繰り返しにはなりますが、includesメソッドの引数はモデル名ではなく関連名です。不安な場合はkeywordのモデルファイル(models/keyword.rb)でhas_manyの部分を確認してみてください。)
この状態で再度キーワード一覧画面にアクセスすると、以下のようにSQLが発行されていることがわかります。
Keyword Load (0.2ms) SELECT “keywords”.* FROM “keywords” ORDER BY “keywords”.”updated_at” DESC LIMIT ? OFFSET ? [[“LIMIT”,10],[“OFFSET”,0]]
KeywordRelationship Load (0.2ms) SELECT "keyword_relationships".* FROM "keyword_relationships"."keyword_id" IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) [["keyword_id", 10], ["keyword_id", 9], ["keyword_id", 8], ["keyword_id", 7], ["keyword_id", 6], ["keyword_id", 5], ["keyword_id", 4], ["keyword_id", 3], ["keyword_id", 2], ["keyword_id", 1]]
Spot Load (0.2ms) SELECT "spots".* FROM "spots" WHERE "spots"."id" IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) [["id", 12], ["id", 11], ["id", 10], ["id", 9], ["id", 8], ["id", 7], ["id", 6], ["id", 5], ["id", 4], ["id", 3], ["id", 2], ["id", 1]]
(0.2ms) SELECT COUNT(*) FROM "spots" INNER JOIN "keyword_relationships" ON "spots"."id" = "keyword_relationships"."spot_id" WHERE "keyword_relationships"."keyword_id" = ? [["keyword_id", 10]]
(0.1ms) SELECT AVG("spots"."rate") FROM "spots" INNER JOIN "keyword_relationships" ON "spots"."id" = "keyword_relationships"."spot_id" WHERE "keyword_relationships"."keyword_id" = ? [["keyword_id", 10]]
CACHE (0.0ms) SELECT AVG("spots"."rate") FROM "spots" INNER JOIN "keyword_relationships" ON "spots"."id" = "keyword_relationships"."spot_id" WHERE "keyword_relationships"."keyword_id" = ? [["keyword_id", 10]]
CACHE (0.0ms) SELECT AVG("spots"."rate") FROM "spots" INNER JOIN "keyword_relationships" ON "spots"."id" = "keyword_relationships"."spot_id" WHERE "keyword_relationships"."keyword_id" = ? [["keyword_id", 10]]
(0.2ms) SELECT COUNT(*) FROM "spots" INNER JOIN "keyword_relationships" ON "spots"."id" = "keyword_relationships"."spot_id" WHERE "keyword_relationships"."keyword_id" = ? [["keyword_id", 9]]
(0.1ms) SELECT AVG("spots"."rate") FROM "spots" INNER JOIN "keyword_relationships" ON "spots"."id" = "keyword_relationships"."spot_id" WHERE "keyword_relationships"."keyword_id" = ? [["keyword_id", 9]]
CACHE (0.0ms) SELECT AVG("spots"."rate") FROM "spots" INNER JOIN "keyword_relationships" ON "spots"."id" = "keyword_relationships"."spot_id" WHERE "keyword_relationships"."keyword_id" = ? [["keyword_id", 9]]
CACHE (0.0ms) SELECT AVG("spots"."rate") FROM "spots" INNER JOIN "keyword_relationships" ON "spots"."id" = "keyword_relationships"."spot_id" WHERE "keyword_relationships"."keyword_id" = ? [["keyword_id", 9]]
(0.2ms) SELECT COUNT(*) FROM "spots" INNER JOIN "keyword_relationships" ON "spots"."id" = "keyword_relationships"."spot_id" WHERE "keyword_relationships"."keyword_id" = ? [["keyword_id", 8]]
(0.1ms) SELECT AVG("spots"."rate") FROM "spots" INNER JOIN "keyword_relationships" ON "spots"."id" = "keyword_relationships"."spot_id" WHERE "keyword_relationships"."keyword_id" = ? [["keyword_id", 8]]
CACHE (0.0ms) SELECT AVG("spots"."rate") FROM "spots" INNER JOIN "keyword_relationships" ON "spots"."id" = "keyword_relationships"."spot_id" WHERE "keyword_relationships"."keyword_id" = ? [["keyword_id", 8]]
CACHE (0.0ms) SELECT AVG("spots"."rate") FROM "spots" INNER JOIN "keyword_relationships" ON "spots"."id" = "keyword_relationships"."spot_id" WHERE "keyword_relationships"."keyword_id" = ? [["keyword_id", 8]]
・・・
(以下、keyword_id = 1まで上記の繰り返し)
includesメソッドを用いたことによって、3行目でspotsテーブルにアクセスしている部分の「N+1問題」が解消されていることがわかります。
しかし、4行目以降のスポット評価平均値計算の部分についてはまだ解消されていません。
これを解消するためには、冒頭でも述べたjoinメソッドおよびgroupメソッドの合わせ技が必要となります。
4. 解決法(後半)
それではキーワード一覧画面における「N+1問題」を完全に解決するための記述法を紹介します。
まずはコントローラーファイルにおける記述です。
def index
@q = Keyword.ransack(params[:q])
@q.sorts = 'updated_at desc' if @q.sorts.empty?
@keywords = @q.result.page(params[:page]).includes(:spots)
@rate_avg = @keywords.joins(:spots).group("keywords.id").average(:rate)
end
これまでと違うのは一番下の@rate_avgの行です。
まずjoinsメソッドですが、**「関連するテーブル同士を内部結合するメソッド」**です。
(具体的な説明に関しては https://qiita.com/yuta-pharmacy2359/items/cf30a20fbea9347c0b72 の記事をご覧ください。)
今回はkeyword_relationshipsテーブルという中間テーブルの存在がありますが、基本的な考え方は同じです。
3つのテーブルで、カラムの内容が一致しているもの(spotsテーブルのidとkeyword_relationshipsテーブルのspot_id、keywordsテーブルのidとkeyword_relationshipsテーブルのkeyword_id)で対応させます。
各テーブルが上図のように対応するはずです。
さらにkeyword_relationshipsテーブルはspotsテーブルおよびkeywordsテーブルの仲介役に過ぎず最終的に必要なデータではないこと、またこのままではspotsテーブルおよびkeywordsテーブルの関係が見づらいので、以下のように書き換えます。
だいぶ両者の関係が見やすくなりました。
そして今回求めているのは**「1つのキーワードにおけるスポット評価の平均値」**なので、上図の赤色で囲った部分が最終的に必要な情報となります。
ここで、前回 (https://qiita.com/yuta-pharmacy2359/items/cf30a20fbea9347c0b72) 取り上げた「1人のユーザーが獲得した総いいね数表示」では、joinsメソッドで結合した時点で既に集計対象であるユーザーのidがユーザー詳細画面で表示されているユーザーに限定されており、あとはそのままcountメソッドでレコード数を集計すればOKでした。
一方今回は、上図の通り集計対象がキーワード一覧画面に表示する全てのキーワード(上図の例ではid=1~10)であり、集計前にキーワードのidごとにグループ分けする必要があります。
そんな時に活躍するのがgroupメソッドです。
groupメソッドは**「指定したカラムのデータの種類(または条件式)ごとにデータをまとめるメソッド」**です。
基本的な定義は以下の通りです。
モデル名.group(：カラム(または"条件式"))
そしてこのgroupメソッドですが、あくまで「データを引数で指定した法則に従ってまとめるだけ」のメソッドであるため、それ単体で利用されることはほとんどなく、countメソッドやavgメソッドなど、集計系のメソッドと併用することが多いです。
それを踏まえると、改めてgroupメソッドの基本的な定義は以下の通りになります。
# avgメソッドを使用する場合
モデル名.group(：カラム(または"条件式")).avg(:カラム(または"条件式"))
続いてそれぞれの引数に関してですが、今回の場合は以下の2条件
・キーワードのidごとに集計したい
・スポットの評価の平均値を求めたい
と、spotsおよびkeywordsそれぞれのテーブルの関係性を考慮すると、
・groupメソッドの引数: "keywords.id"
・avgメソッドの引数: :rate
となります。イメージとしては下図のようになるかと思います。
ということで、先ほども載せましたが、この部分は
@rate_avg = @keywords.joins(:spots).group("keywords.id").average(:rate)
と記述することができます。
さらに留意していただきたいのは、groupメソッドと集計系のメソッドを併用した場合の返り値はハッシュの形であるということです。
今回の例の場合、上式の返り値は以下のようになります。
{ 1=>0.4e1, 2=>0.433333e1, 3=>0.366666e1, 4=>0.4e1, 5=>0.3e1, 6=>0.45e1, 8=>0.1e1, 9=>0.1e1, 10=>0.5e1 }
(ターミナル上ではこのように指数表記で表示されます。例えばキーワードid=1では、0.4e1は4と同値です。また、id=7のように値がnilであった場合は表示されません。)
ということで、最後の課題はこのハッシュからどうやって必要な値を取り出すかということになります。
ここで活躍するのがfetchメソッドです。
fetchメソッドは、ハッシュから引数に指定したキーの値を取り出すメソッドです。
基本的な定義は以下の通りです。
ハッシュ.fetch(key)
たとえば、今回の例では、key=1の場合は0.4e1(表示上は4.0)、key=2の場合は0.433333e1(表示上は4.33)が返ってくるというわけです。
一方、存在しないキー(今回の例では特にkey=7)を引数に取るとエラーとなりますので注意してください。
それを踏まえて、ビューファイルのほうを確認してみましょう。
<% @keywords.each do |keyword| %>
<tr>
(中略)
<td>
<%= keyword.spots.size %>スポット
</td>
<% if @rate_avg.has_key?(keyword.id) %>
<td><%= @rate_avg.fetch(keyword.id).round(2) %></td>
<script>
if(!$("#star-rate-<%= keyword.id %> img").length) {
$('#star-rate-<%= keyword.id %>').raty({
size: 36,
starOff: '<%= asset_path('star-off.png') %>',
starOn: '<%= asset_path('star-on.png') %>',
starHalf: '<%= asset_path('star-half.png') %>',
readOnly: true,
score: <%= @rate_avg.fetch(keyword.id) %>,
});
}
</script>
<% else %>
<td>評価なし</td>
<% end %>
</tr>
<% end %>
まず、if文の部分ですが、先ほども述べた通り、fetchメソッドでは存在しないキーを引数に取ることができないため、まずは@rate_avgのハッシュの中に各キーワードのidがキーとして存在するかどうかをhas_key?メソッドで確認します。
存在する場合は、その直後の文でハッシュからfetchメソッドでそれぞれのキーワードidに対応するスポット評価平均値を取り出して表示します。ない場合はelse以下の文で「評価なし」を表示します。
あとは大方N+1問題を解決しない時の記述と同じですが、もう1点だけ注意すべき記述があります。
<%= keyword.spots.size %>スポットの部分です。
一見、「1つのキーワードに紐づくスポットの数」なので<%= keyword.spots.count %>スポットと記述したくなりますが、countメソッドの性質に落とし穴があります。
countメソッドは、キャッシュを利用しない関係上、表示するキーワードごとに毎回データベースにアクセスしてしまいます。
(参考: https://www.lanches.co.jp/blog/3199)
そのため、countメソッドを利用した場合、ターミナルのログを確認すると、
(0.3ms) SELECT AVG(rate) AS average_rate, keywords.id AS keywords_id FROM "keywords" INNER JOIN "keyword_relationships" ON "keyword_relationships"."keyword_id" = "keywords"."id" INNER JOIN "spots" ON "spots"."id" = "keyword_relationships"."spot_id" GROUP BY keywords.id ORDER BY "keywords"."updated_at" DESK LIMIT ? OFFSET ? [["LIMIT", 10],["OFFSET", 0]]
Keyword Load (0.2ms) SELECT “keywords”.* FROM “keywords” ORDER BY “keywords”.”updated_at” DESC LIMIT ? OFFSET ? [[“LIMIT”,10],[“OFFSET”,0]]
KeywordRelationship Load (0.2ms) SELECT "keyword_relationships".* FROM "keyword_relationships"."keyword_id" IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) [["keyword_id", 10], ["keyword_id", 9], ["keyword_id", 8], ["keyword_id", 7], ["keyword_id", 6], ["keyword_id", 5], ["keyword_id", 4], ["keyword_id", 3], ["keyword_id", 2], ["keyword_id", 1]]
Spot Load (0.2ms) SELECT "spots".* FROM "spots" WHERE "spots"."id" IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) [["id", 12], ["id", 11], ["id", 10], ["id", 9], ["id", 8], ["id", 7], ["id", 6], ["id", 5], ["id", 4], ["id", 3], ["id", 2], ["id", 1]]
(0.2ms) SELECT COUNT(*) FROM "spots" INNER JOIN "keyword_relationships" ON "spots"."id" = "keyword_relationships"."spot_id" WHERE "keyword_relationships"."keyword_id" = ? [["keyword_id", 10]]
(0.2ms) SELECT COUNT(*) FROM "spots" INNER JOIN "keyword_relationships" ON "spots"."id" = "keyword_relationships"."spot_id" WHERE "keyword_relationships"."keyword_id" = ? [["keyword_id", 9]]
(0.2ms) SELECT COUNT(*) FROM "spots" INNER JOIN "keyword_relationships" ON "spots"."id" = "keyword_relationships"."spot_id" WHERE "keyword_relationships"."keyword_id" = ? [["keyword_id", 8]]
(0.2ms) SELECT COUNT(*) FROM "spots" INNER JOIN "keyword_relationships" ON "spots"."id" = "keyword_relationships"."spot_id" WHERE "keyword_relationships"."keyword_id" = ? [["keyword_id", 7]]
(0.2ms) SELECT COUNT(*) FROM "spots" INNER JOIN "keyword_relationships" ON "spots"."id" = "keyword_relationships"."spot_id" WHERE "keyword_relationships"."keyword_id" = ? [["keyword_id", 6]]
(0.2ms) SELECT COUNT(*) FROM "spots" INNER JOIN "keyword_relationships" ON "spots"."id" = "keyword_relationships"."spot_id" WHERE "keyword_relationships"."keyword_id" = ? [["keyword_id", 5]]
(0.2ms) SELECT COUNT(*) FROM "spots" INNER JOIN "keyword_relationships" ON "spots"."id" = "keyword_relationships"."spot_id" WHERE "keyword_relationships"."keyword_id" = ? [["keyword_id", 4]]
(0.2ms) SELECT COUNT(*) FROM "spots" INNER JOIN "keyword_relationships" ON "spots"."id" = "keyword_relationships"."spot_id" WHERE "keyword_relationships"."keyword_id" = ? [["keyword_id", 3]]
(0.2ms) SELECT COUNT(*) FROM "spots" INNER JOIN "keyword_relationships" ON "spots"."id" = "keyword_relationships"."spot_id" WHERE "keyword_relationships"."keyword_id" = ? [["keyword_id", 2]]
(0.2ms) SELECT COUNT(*) FROM "spots" INNER JOIN "keyword_relationships" ON "spots"."id" = "keyword_relationships"."spot_id" WHERE "keyword_relationships"."keyword_id" = ? [["keyword_id", 1]]
このように、各キーワードにおけるスポット数の集計で、1つのキーワードごとにいちいち1回ずつアクセスを行うという無駄が発生してしまいます。
前回紹介した「(ユーザー詳細画面における)1人のユーザーが獲得した総いいね数」のように、集計対象が単一である場合ならcountメソッドでも全く問題ありませんが、一覧画面のように集計対象が複数ある場合は、countメソッドではなくsizeメソッドを使用するようにしましょう。
そこまで対処したあとに再度キーワード一覧画面を表示すると、
(0.3ms) SELECT AVG(rate) AS average_rate, keywords.id AS keywords_id FROM "keywords" INNER JOIN "keyword_relationships" ON "keyword_relationships"."keyword_id" = "keywords"."id" INNER JOIN "spots" ON "spots"."id" = "keyword_relationships"."spot_id" GROUP BY keywords.id ORDER BY "keywords"."updated_at" DESK LIMIT ? OFFSET ? [["LIMIT", 10],["OFFSET", 0]]
Keyword Load (0.3ms) SELECT “keywords”.* FROM “keywords” ORDER BY “keywords”.”updated_at” DESC LIMIT ? OFFSET ? [[“LIMIT”,10],[“OFFSET”,0]]
KeywordRelationship Load (0.2ms) SELECT "keyword_relationships".* FROM "keyword_relationships"."keyword_id" IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) [["keyword_id", 10], ["keyword_id", 9], ["keyword_id", 8], ["keyword_id", 7], ["keyword_id", 6], ["keyword_id", 5], ["keyword_id", 4], ["keyword_id", 3], ["keyword_id", 2], ["keyword_id", 1]]
Spot Load (0.2ms) SELECT "spots".* FROM "spots" WHERE "spots"."id" IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) [["id", 12], ["id", 11], ["id", 10], ["id", 9], ["id", 8], ["id", 7], ["id", 6], ["id", 5], ["id", 4], ["id", 3], ["id", 2], ["id", 1]]
これでキーワード一覧における「N+1問題」を全て解消することができました。
終わりに
長くなりましたが、joinメソッドとgroupメソッドを併用した「N+1問題」の解決方法を紹介しました。
当記事を含め3記事にわたって「N+1問題の解決法」について取り上げましたが、これで大方解決できるかと思いますので、当問題でお悩みの方はぜひ試してみてください。