#概要
この記事は、実際に私がBooQsで運用している「ランキング機能」を例にして、Railsのテーブル結合（内部結合と外部結合）を解説する記事です。
#前提条件
Rails 5.1
#【🙅♂️Before】テーブル結合を使わないランキング機能
私が開発しているBooQsという英単語学習サービスでは、ゲーミフィケーションを用いてユーザーの学習意欲を高めるために、英単語の解答数（userのもつanswer_historiesの数）によるユーザーのランキング機能を実装しています。
しかし、以下のNewrelicのダッシュボードを見ていただくとわかるように、自分の書いたランキング機能の処理速度はお世辞にも良いものとは言えませんでした。
それではまず、自分が組んだ（下手くそな）コードから見ていきましょう。
恥ずかしながら、自分はテーブル結合をよく理解していなかったため、次のようなまどろっこしいコードでランキング機能を実装していました。
def user_ranking
## ここから！！
rankers = []
user_ids = AnswerHistory.where(created_at: Time.now.all_month).group(:user_id).order('count(user_id) desc').pluck(:user_id)
user_ids.each do |id|
#user_idがnilである非ログインユーザーの解答履歴を除外する。
if id.present?
user = User.find(id)
rankers << user
end
end
@users = rankers.paginate(page: params[:page], per_page: 10)
## ここまでがわいの書いたうんこーど！！！💩💩💩
#ページネーションの位置に応じて、最初に表示する順位を調整するための処理
if params[:page].present?
@base_of_ranking = params[:page].to_i*10+1-10
else
@base_of_ranking = 1
end
end
以下に、modelとviewも記載します。
####model
class User < ApplicationRecord
has_many :answer_histories, dependent: :destroy
end
class AnswerHistory < ApplicationRecord
# 非ログインユーザーの解答記録は、user_idをnilで記録する。
belongs_to :user, optional: true
end
####View
<% provide(:title, "月間ランキング") %>
<%= render "home/navbar" %>
<div class="box users_index">
<div class="wrapper">
<div class="headline-green">
<h1 class="center green"><i class="fas fa-user-crown"></i>解答数ランキング</h1>
</div>
<p> </p>
<div class="quiz_tab">
<%= link_to home_user_ranking_path, class: "left btn green" do %>
月間
<% end %>
<%= link_to home_user_ranking_weekly_path, class: "right btn" do %>
週間
<% end %>
</div>
<ul class="users">
<% @users&.each_with_index do |user, i| %>
<% i += @base_of_ranking %>
<div class="whole_link">
<li class="user-feed">
<%= link_to user_path(user) do %>
<%= icon_for user, size: 50 %>
<% end %>
<div class="right-side">
<div class="name">
<%= link_to user do %>
<% if user.premium_member? %>
<i class="fas fa-crown non-margin"></i>
<% end %>
<%= user.name %>
<% end %>
</div>
<p>ランク：<b><%= i %>位</b></p>
<p>月間解答数：<b><%= user.answer_histories.where(created_at: Time.now.all_month).count %></b></p>
<p>継続日数：<b><%= running_days_count(user) %></b></p>
<p>レベル：<b>Lv.<%= user.current_level.floor %></b></p>
<div class="follow_btn"><%= render 'users/follow_form', user: user %></div>
</div>
</li>
<a href="<%= user_path(user) %>" class="link"></a>
</div>
<% end %>
</ul>
<div class="center">
<%= will_paginate @users,
previous_label: '← 前へ', next_label: '次へ →',
page_links: false %>
</div>
</div>
</div>
実際のランキングページは以下になります。
ランキングページ： https://www.booqs.net/ranking/monthly
#【🙆♂️After】内部結合を使ったランキング機能
Beforeのテーブル結合を利用しない方法だと、user_idsを取得するためにクエリを発行し、さらにユーザーを取得するたびに、user = User.find(id)でクエリが発行されるため、処理が遅くなります。
そのため、知り合いのベテランの開発者の方に、下のような方法をオススメいただきました。
def user_ranking
## ここから！！！
@users = User.joins(:answer_histories).where(answer_histories: {created_at: Time.now.all_month})
.group(:id).order('count(answer_histories.user_id) desc')
.paginate(page: params[:page], per_page: 10)
## ここまでが修正いただいたコード！！！😍😍😍
#ページネーションの位置に応じて、最初の順位を調整するための処理
if params[:page].present?
@base_of_ranking = params[:page].to_i*10+1-10
else
@base_of_ranking = 1
end
end
めちゃくちゃスッキリ！！
さらにクエリも一発でとても効率的...!!
ただこのコードをコピペするだけでは自分の勉強にはならないので、
ここからは、これらの処理がどんなことをしているのかを１つずつ丁寧に解説していきます。
##内部結合【.joins(:answer_histories)】
.joins(:answer_histories)を利用することで、usersテーブルとanswer_historiesテーブルを結合して、データベースを検索できるようになります。
つまり、２つのテーブル同士を合体させて、１つのテーブルをつくれるのですね！
BooQsの例で、解説しましょう。
まず、BooQsには以下のように、
会員登録したユーザーのデータを格納するusersテーブルと、そのユーザーが解いた問題のデータを格納するanswer_historiesテーブルがあります。
#####usersテーブル
| id | name |
|---|---|
| 1 | 清水さん |
| 2 | 小林さん |
| 3 | 長谷川さん |
| 4 | 山田さん |
#####anwser_historiesテーブル
| id | quiz_id | user_id |
|---|---|---|
| 1 | 104 | 4 |
| 2 | 89 | nil |
| 3 | 95 | 2 |
| 4 | 184 | 4 |
| 5 | 43 | 1 |
| 6 | 205 | 1 |
| 7 | 21 | nil |
| 8 | 76 | 1 |
| 9 | 164 | 1 |
（**補足：**user_idがnilであるanswer_historiesは、ログインしていないユーザーの解答履歴です。）
そして、
User.joins(:answer_histories)を利用することによって、answer_historiesテーブルの外部キー（user_id）に基づいて、２つのテーブルを結合することができます。
では、実際に見てみましょう。
#####usersテーブルにanswer_historiesテーブルを内部結合したテーブル
| id | name | id | quiz_id | user_id |
|---|---|---|---|---|
| 4 | 山田さん | 1 | 104 | 4 |
| 2 | 小林さん | 3 | 95 | 2 |
| 4 | 山田さん | 4 | 184 | 4 |
| 1 | 清水さん | 5 | 43 | 1 |
| 1 | 清水さん | 6 | 205 | 1 |
| 1 | 清水さん | 8 | 76 | 1 |
| 1 | 清水さん | 9 | 164 | 1 |
右端のanswer_historisの外部キー（user_id）に基づいて、
きちんと２つのテーブルがつながりましたね！
結合結果のテーブルの中ですぐに目につく変化としては、次の３つがありますね！
- answer_historiesのuser_id（外部キー）の数だけ山田さんと清水さんが増殖した。
- answer_historiesのuser_idに存在しない長谷川さんは排除された。
- user_idがnilであるanswer_historiesのidが2と7のレコードが排除された。
（**注意：**例ではanswer_historiesの主キー（id）の昇順でレコードを並べていますが、私の調べた限りでは、結合結果のレコードの順序については、特定のidを基準にした昇順・降順などはないようでした。もし私が間違っていたら誰かぜひ教えてください。）
このようなjoinsメソッドを利用したテーブル同士の結合方法は、**【内部結合】**と呼ばれます。
内部結合では、結合条件に合致しないレコードは排除されます。
内部結合の結合条件とは、結合先の外部キー = 結合元の主キーです。
BooQsの例で紹介しましょう。
たとえば、今回のUser.joins(:answer_histories)によって内部結合を行うと、次のようなSQLが発行されます。
SELECT "users".* FROM "users"
INNER JOIN "answer_histories" ON "answer_histories"."user_id" = "users"."id"
上記SQLの**ON "answer_histories"."user_id" = "users"."id"**の部分が、テーブルの結合条件となります。
つまり、answer_historiesレコードの外部キー（user_id）とusersレコードの主キー（id）が一致するかどうかが、レコード同士を結合する条件となるのですね。
そして内部結合においては、この結合条件に合致しないusersテーブルとanswer_historiesテーブルのレコードは、結合結果のテーブルから排除されています。
具体的に見ていきましょう。
上の結合されたテーブルをじっくりと眺めてください。
きっと次の２種類のレコードが排除されていることがわかるはずです。
- answer_historiesの外部キー（user_id）に存在しないusersレコード（例：長谷川さんのレコードが排除されている）
- 外部キーがnilであるanswer_historiesレコード（例：idが2と7のレコードが排除されている）
具体例があると、きっとわかりやすいと思います。
別の表現で説明すると、内部結合では、以下に示すようなテーブル結合結果にはならない ということです。
（以下で紹介する例は、テーブル結合のうち**「外部結合」の解説**でもあります。
ランキング機能では「内部結合」を利用するので、読み飛ばしていただいても構いませんが、読んでおくと、ランキングで利用する「内部結合」についても理解が深まるはずです。）
####【こうはならない！！】answer_historiesの外部キーに存在しないusersレコードまで結合される【左外部結合】
| id | name | id | quiz_id | user_id |
|---|---|---|---|---|
| 4 | 山田さん | 1 | 104 | 4 |
| 2 | 小林さん | 3 | 95 | 2 |
| 4 | 山田さん | 4 | 184 | 4 |
| 1 | 清水さん | 5 | 43 | 1 |
| 1 | 清水さん | 6 | 205 | 1 |
| 1 | 清水さん | 8 | 76 | 1 |
| 1 | 清水さん | 9 | 164 | 1 |
| 3 | 長谷川さん | nil | nil | nil |
上記のテーブルでは、answer_historiesレコードのuser_idに存在しないはずの「長谷川さん」までテーブルに結合されています。
長谷川さんは人生でただ一回もBooQsで問題を解いてくれなかったので、長谷川さんに結合されたanswer_historiesレコードのデータは、なんと、すべてnilです！！（解いてよ！！！🥺）
これは内部結合ではありません。
これは**【外部結合】と呼ばれています。
さらに言えば、外部結合のうち、【左外部結合】**と呼ばれるテーブル結合方法です。
内部結合と外部結合の区別は簡単です。
外部結合では、内部結合では排除されていた「結合条件に合致しないレコード」まで結合することができます。
外部結合には、**【左外部結合】**と **【右外部結合】**の２種類がありますが、こちらもいかめしい文字面よりは、区別も簡単です。
左外部結合は、結合条件に合致しない結合『元』のレコードを結合します。
右外部結合は、結合条件に合致しない結合『先』のレコードを結合します。
今回の例でいえば、長谷川さんという、結合条件に合致しない「usersテーブル（結合元）のレコード」を結合しています。
だから、【左外部結合】なのですね。
Railsによる左外部結合は、次のように行えます。
User.joins("LEFT OUTER JOIN answer_histories ON users.id = answer_histories.user_id")
#Rails5.0以降は、次のメソッドで右外部結合を行うこともできます。
User.left_outer_joins(:answer_histories)
####【こうはならない！！】外部キーがnilであるanswer_historiesレコードまで結合される【右外部結合】
| id | name | id | quiz_id | user_id |
|---|---|---|---|---|
| 4 | 山田さん | 1 | 104 | 4 |
| nil | nil | 2 | 89 | nil |
| 2 | 小林さん | 3 | 95 | 2 |
| 4 | 山田さん | 4 | 184 | 4 |
| 1 | 清水さん | 5 | 43 | 1 |
| 1 | 清水さん | 6 | 205 | 1 |
| nil | nil | 7 | 21 | nil |
| 1 | 清水さん | 8 | 76 | 1 |
| 1 | 清水さん | 9 | 164 | 1 |
上記のテーブルでは、user_idがnilであるanswer_historiesレコードも結合されてしまっています。
user_idがnilということは、これは「会員登録していないユーザーの解答記録」ということなので、当然、結合されたusersレコードのデータもすべてnilです（会員登録してよ！！！🥺）。
このテーブルを生成する結合方法は、**「外部結合」のうち、「右外部結合」**と呼ばれています。
先ほど説明したように、結合条件に合致しない、結合『先』のレコードを結合するので、右外部結合なのですね。
answer_historiesテーブルは、結合先のテーブルです。
右外部結合は、Railsでは次のコードで行えます。
User.joins("RIGHT OUTER JOIN answer_histories ON users.id = answer_histories.user_id")
（Railsの右外部結合については、なぜか１つも解説記事が見当たりませんでした。
この記事がRailsによる右外部結合について触れた最初の記事かもしれません。
外部結合（左外部結合＆右外部結合）をきちんと理解されたいなら、以下の記事を参考にされると良いでしょう。
SQL素人でも分かるテーブル結合(inner joinとouter join)）
ランキング機能をつくる場合、『内部結合によるレコードの排除』はとても都合の良い処理です。
なぜなら、排除されるanswer_historiesの外部キーに存在しないusersレコードとは、すなわち、**「1問も問題を解いていないランキング圏外のユーザーのデータ」であり、
外部キーがnilであるanswer_historiesレコードとは、すなわち、「ログインしていないユーザーの解答記録」**であるため、ランキングをつくる上で考慮する必要のないデータだからです。
###結合先のレコードの絞り込み【.where(answer_histories: {created_at: Time.now.all_month})】
Where句を使って、結合先のカラムの値を条件にして、レコードを絞り込むことも可能です。
その場合、
where(結合先のテーブル: {結合先のテーブルのカラム： 値})という形で行います。
BooQsの例で言えば、**『月間の』**解答数ランキングを表示したかったので、次のように結合先のデータを絞り込んでいます。
@users = User.joins(:answer_histories).where(answer_histories: {created_at: Time.now.all_month})
###ランキングの順位順にusersレコードを並べ替える【.group(:id).order("count(answer_histories.user_id) DESC")】
さて、ランキング圏内のユーザーのレコードはすべて揃えたので、最後にこれを順位順に並べ替えましょう。
ここまで内部結合を使って作成したテーブルは次のようになっていますよね。
| id | name | id | quiz_id | user_id |
|---|---|---|---|---|
| 4 | 山田さん | 1 | 104 | 4 |
| 2 | 小林さん | 3 | 95 | 2 |
| 4 | 山田さん | 4 | 184 | 4 |
| 1 | 清水さん | 5 | 43 | 1 |
| 1 | 清水さん | 6 | 205 | 1 |
| 1 | 清水さん | 8 | 76 | 1 |
| 1 | 清水さん | 9 | 164 | 1 |
明かに、清水さんが1位、山田さんが2位、小林さんが3位という風にわかりますが、
これをプログラムで集計するためには、groupメソッドでユーザーごとにレコードをまとめる必要があります。
BooQsの例でいえば、.group(:id)でそれぞれのユーザーのレコードをまとめらていますね。
さて、groupメソッドでまとめたら、それぞれのグループがもつレコードの数で順位をつければ、ランキングを完成させることができます。
ここで使われるのが、orderメソッドです。
orderメソッド自体は、馴染みのある方ばかりでしょうが、実はこのorderメソッド、中身でSQLも利用できます。
そのため、groupメソッドによるGROUP BY、orderメソッドによるORDER BYの２つを組み合わせて次のようにすれば、**「レコードの多い順（降順）にユーザーを並べ替え」**ができ、結果としてランキングの順位でユーザーを並べることができます。
@users = User.joins(:answer_histories).where(answer_histories: {created_at: Time.now.all_month})
.group("id").order("count(answer_histories.user_id) DESC")
orderのなかで、.order("count(answer_histories.user_id) DESC")という風に条件を指定していることに注目してください。
この"count([数えたい要素]) 並び順"という指定方法は、RubyでもRailsでもなく、SQLによる指定方法です。
発行されるSQLのクエリは、次のようになっています。
SELECT "users".* FROM "users"
INNER JOIN "answer_histories" ON "answer_histories"."user_id" = "users"."id"
GROUP BY "users"."id" ORDER BY count(answer_histories.user_id) DESC
このようにgroupメソッドとorderメソッドをうまく使うことで、ランキングの順位でユーザーを並べ替えることができます。
| id | name |
|---|---|
| 1 | 清水さん |
| 4 | 山田さん |
| 2 | 小林さん |
あとはこれを上記のviewでeach_with_indexなどで取り出してあげれば、ランキング機能の完成です。
お疲れさまでした！！
ランキングページ： https://www.booqs.net/ranking/monthly
##これだけ教えてください🙇♂️
これだけ本当にわからなかったので、わかる方がいらしたらぜひ教えてください。。。
内部結合したあとの、users.idとanswer_histories.user_idは同じはずだと思います。
なので私は、下のコードは、@users = User.joins(:answer_histories) .group("id").order("count(answer_histories.user_id) DESC") と全く同じだと思っていました。
@users = User.joins(:answer_histories)
.group("answer_histories.user_id").order("count(answer_histories.user_id) DESC")
しかし、このコードを実行するとActiveRecord::StatementInvalid (PG::GroupingError: ERROR: column "users.id" must appear in the GROUP BY clause or be used in an aggregate function というエラーが表示されてしまいます。
つまり、group（GROUP BY）のなかに「users.id」を利用しろと言われてしまいます。
なぜ、groupのなかは、"answer_histories.user_id"ではダメで、"id" でなくてはならないのでしょうか？？
何卒ご教示いただければ幸いです...!🙇♂️