はじめに
Railsを利用していると、普通に使っている分にはどんなSQLが発行されるんだろう？ということを考えずとも、欲しいレコードが簡単に取得できますよね。Railsを学び始めた頃からすごいな〜と思っていましたが、今になってもやはり凄いなと思います。
ただ１つ、便利だからこそ生じる弊害があります。それは
＞＞＞SQLが書けなくなる＜＜＜
別に、「SQLが書けなくてもActiveRecordとかが頑張ってくれるから困らないじゃん」と思っている方もいらっしゃるかもしれません。私もそう思っていました。
しかし、例えRailsであっても、テーブル構造が複雑になってくると、どんなSQLを発行したいのか？という部分が分からないと、Railsでどう書いたらデータが取れるのか？というのが分からなくなります。
業務中に、そういった場面と直面する機会が最近格段に増え物凄く困った、危機感を持った、というのが私の体験談であり、結果として「やばい、SQL勉強し直そう」と思うきっかけとなりました。
なんとなくで Rails でレコードを取得している方は、この記事で一緒に学び直しましょう！
環境
- Docker for Mac 2.3.0.3
- Ruby 2.6.6
- Rails 6.0.3
- MySQL 8.0
- DBeaver 7.1.0
環境構築
- こちらからリポジトリをcloneする
-
$ docker-compose buildを実行 -
$ docker-compose up -dを実行 -
$ docker-compose exec web rails db:createを実行 -
$ docker-compose exec web rails db:migrateを実行 -
$ docker-compose exec web rails db:seedを実行 -
こちらの記事を参考に、
MySQLとDBeaverを接続する -
DBeaverとの接続後、studentsテーブルにレコードが 6 件登録されていることを確認できればOK
※DBeaver との接続時、MySQL の root パスワードを要求されると思います。
パスワードは docker-compose.ymlのMYSQL_ROOT_PASSWORD に指定した値を入力してください。（未設定の場合は password が設定されます。）
今回使用するテーブル
初級編〜SELECTの基礎を学ぶ〜
※ここからはDBeaverのSQLコンソールを使用していきます。sql_master_developmentがデフォルト選択されるよう設定を行って下さい。
universitiesテーブルのレコードを全件取得する
手始めに universitiesテーブルのレコードを全件取得してみましょう。これは簡単ですね！
universitiesテーブルのレコードを全件取得する
select * from universities;
これは Railsだと以下のようになります。
University.all
特定のUniversityのレコードを取得する
では、次はnameがUniversityAのレコードを全件取得しましょう。
`name`が`UniversityA`のレコードを全件取得する
select * from universities where name = 'UniversityA';
これはRailsだと以下のようなイメージです。
University.where(name: 'UniversityA')
whereが出て来たので、find_byもSQLで書いてみましょう。
※1件のみ取得したい時はlimitを使用します。
`name`が`UniversityA`のレコードを1件取得する
select * from universities where name = 'UniversityA' limit 1;
中級編 テーブルを結合して、欲しいレコードを取得する
UniversityAに紐づいた studentsレコードを全件取得する
テーブル結合について（簡易版）
≈universitiesテーブルとstudentsテーブルはuniversity_studentsという中間テーブルによって紐づけられています。
そのため、該当レコードを取得するためにはテーブル同士の結合が必要になります。
テーブル結合には内部結合（JOIN/INNER JOIN）と外部結合（LEFT JOIN）があります。
- 内部結合・・・あるカラムの値が一致しているレコードを取得し、結合して表示する
- 外部結合・・・あるカラムの値が一致していない場合でもテーブルを結合し、全件表示する
今回のような場合は 内部結合か外部結合か考えながら、まずは最終的に欲しい情報であるstudentsテーブルと中間テーブルである university_studentsテーブルを結合して、studentsレコードを全件取得してみましょう。
※結合の構文は inner join(left join) 結合するテーブル名 on 結合先テーブル名.カラム名 = 結合するテーブル名.カラム名
`name`が`UniversityA`のレコードを1件取得する
select students.* from students inner join university_students on students.id = university_students.student_id;
取得できましたか？
これはRailsだと以下のようになります。
Student.joins(:university_student)
結合したテーブルを使って、UniversityAに紐づいた studentsレコードを全件取得する
では、次は本題であるUniversityAに紐付いたstudentsレコードを全件取得していきましょう。
先ほどstudentsテーブルとuniversity_studentsテーブルを結合したので、今回は追加でuniversity_studentsテーブルとuniversitiesテーブルを結合します。
そして、universitiesレコードのnameカラムがUniversityAであるものを特定すると、UniversityAに紐づいた studentsレコードを全件取得することができます。
StudentA〜StudentFまで取得できていたらOKです 
`UniversityA`に紐づいた `students`レコードを全件取得する
select students.* from students
inner join university_students on students.id = university_students.student_id
inner join universities on university_students.university_id = universities.id
where universities.name = 'UniversityA';
これをRailsで書くと以下のようになります。
Student.joins(:university).where(universities: { name: 'UniversityA' })
この辺りから「Railsスゲー！！」という気持ちが大きくなってくるのではないでしょうか？
joinsが:universityのみの記述で良いのは、Model（Student）で has_one :university, through: :university_studentを定義しているからです。
Railsは関連付けさえ綺麗に定義できれば上記のように記述をどんどん簡略化できるのでよいですね
UniversityAに所属しているStudentAのcourse_registrationsレコードを全件取得する
この辺から関連するテーブルが増えてきてごちゃごちゃします。
一つずつ紐解いて考えていきましょう。
ここでは UniversityA大学に所属しているStudentAさんのcourse_registrationレコード、つまり履修登録情報を取得します。
ここで簡単に仕様を説明します。
course_registrationsレコードは年度毎に作成されます。
StudentAさんは、2020年度の時点で2回生です。
そのため、course_registrationsレコードは2レコード作成されています。
studentsテーブルとcourse_registrationsテーブルはstudent_course_registrationsという中間テーブルを持っています。
`UniversityA`大学に所属している`StudentA`さんの`course_registration`レコードを全件取得する
select course_registrations.* from course_registrations
inner join student_course_registrations on course_registrations.id = student_course_registrations.course_registration_id
inner join students on student_course_registrations.student_id = students.id
inner join university_students on students.id = university_students.student_id
inner join universities on university_students.university_id = universities.id
where universities.name = 'UniversityA'
and students.name = 'StudentA';
これをRailsで書くとこうなります。
CourseRegistration.joins(student: :university)
.where(
students: { name: 'StudentA' },
universities: { name: 'UniversityA' }
)
ここまで書いた私「Railsやばい」
自分で生のSQLを全部書く時間と比較してどうでしょう？Rails凄い。
さて、まだこれはテーブル結合として「まだ」優しいです。
この時点ではまだ、全て年度の履修を登録したという情報しか取得できていません。
最初のER図を見た時に少し嫌な予感がした、という方。正しい判断です。
次は ある年度に履修した全ての科目を取得してみましょう！
UniversityAに所属しているStudentAの 2020年度のCourseRegistration（履修登録情報）に紐付いたsubjectsレコードを全件取得する
見出しがカオスになってきました。
ただ、ここまでの知識を活かせばそう難しくないと思います。
subjectsテーブルとcourse_registrationsテーブルにも、例によってsubject_course_registrationsという中間テーブルがいます。
では、今までの知識を活かして取得してみましょう！
`UniversityA`に所属している`StudentA`の `2020`年度の`CourseRegistration（履修登録情報）`に紐付いた`subjects`レコードを全件取得する
select subjects.* from subjects
inner join subject_course_registrations on subjects.id = subject_course_registrations.subject_id
inner join course_registrations on subject_course_registrations.course_registration_id = course_registrations.id
inner join student_course_registrations on course_registrations.id = student_course_registrations.course_registration_id
inner join students on student_course_registrations.student_id = students.id
inner join university_students on students.id = university_students.student_id
inner join universities on university_students.university_id = universities.id
where universities.name = 'UniversityA'
and students.name = 'StudentA'
and course_registrations.year = '2020';
これをRailsで書くと以下のようになります。
Subject.joins(course_registration: { student: :university })
.where(
universities: { name: 'UniversityA' },
students: { name: 'StudentA' },
course_registrations: { year: '2020' }
)
やっぱりRailsって凄いですね。。。
UniversityAに所属するTeacherAが受け持っている科目 基礎英語 を 2020年度に受講する生徒を全件取得する
subjectsレコードを取得したから、今度はどうせteachersレコードでも取得するんでしょうと思われた方もいるかもしれません。
ですが、恐らくワンパターンすぎて飽きてきたという方もいるでしょう。
私も流石に（ちょっともういいかな……）と思えてきたため、teachersレコードを追加するのは各自で試してみていただければ、と思います。
今度は生徒ではなく、教員側がデータベースに登録された情報を参照したい場合を考えてみます。
表題のようなレコードが欲しい、というケースですが、例えば 教員が 「今年度の出席簿を作りたいな……」と思った時に、履修対象者を全件取得したい、といった時に起こり得そうですね。
`UniversityA`に所属する`TeacherA`が受け持っている科目 `基礎英語` を `2020年度`に受講する生徒を全件取得する
select distinct students.* from students
inner join student_course_registrations on student_course_registrations.student_id = students.id
inner join course_registrations on course_registrations.id = student_course_registrations.course_registration_id
inner join subject_course_registrations on subject_course_registrations.course_registration_id = course_registrations.id
inner join subjects on subjects.id = subject_course_registrations.subject_id
inner join subject_teachers on subject_teachers.subject_id = subjects.id
inner join teachers on teachers.id = subject_teachers.teacher_id
inner join university_teachers on university_teachers.university_id = teachers.id
inner join universities on university_teachers.university_id = universities.id
where universities.name = 'UniversityA'
and teachers.name = 'TeacherA'
and subjects.name = '基礎英語'
and course_registrations.year = '2020';
これをRailsで書くと以下のようになります。
Student.joins(course_registrations: { subjects: { teachers: :university } })
.where(
course_registrations:{
subjects: { name: '基礎英語' },
teachers: { name: 'TeacherA' },
universities: { name: 'UniversityA' },
course_registrations: { year: '2020' }
}
)
上級(?)編 結合したテーブルを使って合計値を出す
各生徒ごとの総取得単位を取得する
ただレコードをとるだけでは面白くないので、次は生徒の名前と、総取得単位を取得し閲覧したいと思います。
取得するのは以下のようなデータです。
| student_name | total_credit |
|---|---|
| StudentA | 13 |
| StudentB | 13 |
| StudentC | 8 |
| StudentD | 7 |
| ・・・ | ・・・ |
今回は 生徒ごとの単位の合計を出すので、studentsテーブルとsubjectsテーブルの情報が必要になります。
１人の生徒には年度ごとに履修登録情報が紐づいていて、履修登録情報に各科目の情報が紐づいている、というのは一度SQLを書いたので問題ないと思います。
キモとなるのは、1人ずつ 単位の合計値をまとめたいという点だと思います。
情報をまとめたい場合は GROUP BYを使います。
group by （カラム名）
また、カラムの合計値を出す時は SUM関数を使います。
sum(カラム)
そして、先程の表を見ると、少しヘッダーの表示がカラム名と異なっていたと思います。
ASでエイリアスをつける必要がありそうですね。
上記のことを踏まえて、SQLを書いてみましょう！
各生徒ごとの総取得単位を取得する
select students.name as student_name, sum(subjects.credit) as sum_credit FROM students
inner join student_course_registrations ON student_course_registrations.student_id = students.id
inner join course_registrations ON course_registrations.id = student_course_registrations.course_registration_id
inner join subject_course_registrations ON subject_course_registrations.course_registration_id = course_registrations.id
inner join subjects ON subjects.id = subject_course_registrations.subject_id
group by students.name
これはRailsで書くと以下のようになります。
Student.joins(course_registrations: :subjects)
.group('students.name')
.sum(:credit)
書く量が全然違いますよね。
ただ、これを書こうと思った時に、結局どんな感じのSQLが発行されて欲しいのか？というところがわからないと、「？？？」となると思います。（私は毎回そうなっていました）
また、「上みたいな場合だとRubyでなんとかできそうだから、mapとかeach_with_object使ってなんとかしちゃお」と私は思いがちだったのですが、純粋に値が必要なだけであればデータベースから直接取得できるので、Rubyでゴリ押すのではなく、いい感じのSQLを発行して必要な値を取得する、というのも必要なスキルだなと思いました
最後に
親子関係のあるテーブルならまだ良いのですが、親子孫曽孫……のような構成のテーブルがあった時に、「親から曽孫ってどうやってとるんだ！？！？」と混乱することが多かったのですが、「どのテーブルのレコードが主人公になっているのか？」を意識しながら書いていくとそんなに複雑ではないということが分かったと思います。
また、Railsでレコードを取得する時も少し混乱してしまいがちですが、SQLのテーブル結合を意識するとシンプルに書いていくことができるので、こんなSQLが発行されて欲しい！というのを意識しながら書いていくと良いですね
Railsでシンプルに書いていこうと思うと、Modelに定義する関連付けが大事だという話を少ししました。テーブルが複雑になればなるほど、この関連付けの定義も難しくなります（ここ最近私が頭を抱えているところです。）
次はRailsで条件付きの has_one・has_manyの定義の仕方を学べるようなQiitaが書けたらと思っています 