今回は特によく使うけれども書き方がよくわからないJOIN句を取り上げます。
対象とするテーブル/モデル
今回は以下のモデルを例にJOIN句を作成してみます。
- Student: 学生
- Exam: 試験
- 学生は複数の試験を課される
Railsであれば以下のようなAssociationを定義するのが普通です。
class Student < ApplicationRecord
has_many :exams
end
class Exam < ApplicationRecord
belongs_to :student
end
このように has_many , belongs_to を定義しておけば、Arelを使わずとも簡単にJOINによる問い合わせが可能です。
| ruby | SQL |
|---|---|
Exam.joins(:student) |
SELECT "exams".* |
Student.joins(:exams) |
SELECT "students".* |
ArelでJOIN句をつくる
モデル側に has_many/belongs_toが定義されていない場合であっても、以下のようにArelを使っておなじSQLを作成でき、おなじ結果を得ることができます。
# Exam.joins(:student) とおなじ
Exam.joins(
Exam.arel_table.join(Student.arel_table).
on(Student.arel_table[:id].eq(Exam.arel_table[:student_id]).
join_sources
)
Exam.joins と Exam.arel_table.join と似通ったものがごちゃごちゃしていて理解が難しいので書き下してみます。
# Exam.joins(:student) とおなじ
exams = Exam.arel_table # Arel::Table
students = Student.arel_table # Arel::Table
join_sources =
exams.join(students).on(students[:id].eq(exams[:student_id])).join_sources
Exam.joins(join_sources)
belongs_to が定義されていれば、Exam.joins に :student を指定するだけでしたが
Arelの場合は複雑な join_sources を渡すことになりました。
join_sourcesの構造
以下に join_sources の構造を示します。
Exam.joins(:student)はExam.joins(_join_sources_)と等価join_sourcesの実体はArel::Nodes::InnerJoinのインスタンスの配列join_sourcesはArel::SelectManager#join_sourcesから生成できるArel::Table#join(arel_table)で JOIN句を生成して自身に保持させるArel::Table#on(arel_attribute)で ON句を生成して自身に保持させる
join_sources が配列になっているのは、JOIN句が複数とることができるからです。
複数のJOIN句
例えば Student belongs_to School と定義されている場合
Exam.joins(student: :school)
SELECT
"exams".*
FROM "exams"
INNER JOIN "students" ON "students"."id" = "exams"."student_id"
INNER JOIN "schools" ON "schools"."id" = "students"."school_id"
となりJOIN句が2つありますが、Arelの場合は以下のように書くことができます。
exams = Exam.arel_table # Arel::Table
students = Student.arel_table # Arel::Table
schools = School.arel_table # Arel::Table
join_sources = exams.
join(students).on(students[:id].eq(exams[:student_id])).
join(schools).on(schools[:id].eq(students[:school_id])).
join_sources
Exam.joins(join_sources)
OUTER JOIN
LEFT OUTER JOIN の例です
Railsでは Exam.left_joins(:student) と書けます。
exams = Exam.arel_table # Arel::Table
students = Student.arel_table # Arel::Table
join_sources = exams.
join(students, Arel::Nodes::OuterJoin).on(students[:id].eq(exams[:student_id])).
join_sources
Exam.joins(join_sources)
SELECT "exams".*
FROM "exams"
LEFT OUTER JOIN "students"
ON "students"."id" = "exams"."student_id"
他にも Arel::Nodes::RightOuterJoin, Arel::Nodes::FullOuterJoin が使えます。
複合主キーの場合
複合主キーで定義されるモデルとJOINしたい場合、has_many/belongs_toでは定義できませんがArelを使ってJOINすることができます。
- 学生は、入学すると「出席番号(
attendance_no)」が1番から発行されます。 - 学生を特定するには、「出席番号(
attendance_no)」と「入学年度(year_of_enrollment)」が必要となります。 - 各試験結果には、「学生の入学年度(
student_year)」と「出席番号(student_no)」が記載されているものとします。
学生ごとに試験結果を出力する場合以下のようなSQLを発行します。
SELECT "exams".*
FROM "exams"
INNER JOIN "students"
ON "exams"."student_year" = "students"."year_of_enrollment"
AND
"exams"."student_no" = "students"."attendance_no"
Arelでは以下のように実装できます。
exams = Exam.arel_table
students = Student.arel_table
join_sources =
exams.join(students).
on(
exams[:student_year].eq(students[:year_of_enrollment]).and(
exams[:student_on].eq(students[:attendance_no])
).join_sources
Exam.joins(join_sources)
小技ですが、on句は以下のように書くとインデントがキレイにそろいます。
[
exams[:student_year].eq(students[:year_of_enrollment])
exams[:student_on].eq(students[:attendance_no])
].inject(:and)
次回は組み込み関数の活用の予定です。