【初心者向け？】SQL超マスター〜複雑なテーブルにへこたれない編〜【Railsとの比較あり】

## はじめに
`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

## 環境構築
1. [こちら](https://github.com/miwa0519/sql_master)からリポジトリをcloneする
2. `$ docker-compose build`を実行
3. `$ docker-compose up -d`を実行
4. `$ docker-compose exec web rails db:create` を実行
5. `$ docker-compose exec web rails db:migrate`を実行
6. `$ docker-compose exec web rails db:seed` を実行
7. [こちら](https://qiita.com/niki_cat_0714/items/d137db55c2f73ea65791)の記事を参考に、`MySQL`と`DBeaver`を接続する
8. `DBeaver` との接続後、`students` テーブルにレコードが 6 件登録されていることを確認できればOK

※DBeaver との接続時、MySQL の `root` パスワードを要求されると思います。
パスワードは `docker-compose.yml`の`MYSQL_ROOT_PASSWORD` に指定した値を入力してください。（未設定の場合は `password` が設定されます。）

## 今回使用するテーブル

<img width="1098" alt="スクリーンショット 2020-06-23 14.50.09.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/273022/bec69114-8785-5ad6-c06a-00992ae8b0eb.png">


## 初級編〜SELECTの基礎を学ぶ〜
※ここからは`DBeaver`の`SQLコンソール`を使用していきます。`sql_master_development`がデフォルト選択されるよう設定を行って下さい。

### `universities`テーブルのレコードを全件取得する

手始めに `universities`テーブルのレコードを全件取得してみましょう。これは簡単ですね！

<details><summary>universitiesテーブルのレコードを全件取得する</summary><div>

```sql
 select * from universities;
```
</div></details>

これは `Rails`だと以下のようになります。

```ruby
University.all
```

### 特定のUniversityのレコードを取得する
では、次は`name`が`UniversityA`のレコードを全件取得しましょう。

<details><summary>`name`が`UniversityA`のレコードを全件取得する</summary><div>

```sql
 select * from universities where name = 'UniversityA';
```
</div></details>

これは`Rails`だと以下のようなイメージです。

```
University.where(name: 'UniversityA')
```

`where`が出て来たので、`find_by`も`SQL`で書いてみましょう。
※1件のみ取得したい時は`limit`を使用します。

<details><summary>`name`が`UniversityA`のレコードを1件取得する</summary><div>

```sql
 select * from universities where name = 'UniversityA' limit 1;
```
</div></details>

## 中級編 テーブルを結合して、欲しいレコードを取得する
### `UniversityA`に紐づいた `students`レコードを全件取得する

#### テーブル結合について（簡易版）
≈`universities`テーブルと`students`テーブルは`university_students`という中間テーブルによって紐づけられています。


そのため、該当レコードを取得するためにはテーブル同士の結合が必要になります。
テーブル結合には`内部結合（JOIN/INNER JOIN）`と`外部結合（LEFT JOIN）`があります。

- 内部結合・・・あるカラムの値が一致しているレコードを取得し、結合して表示する
- 外部結合・・・あるカラムの値が一致していない場合でもテーブルを結合し、全件表示する

今回のような場合は `内部結合`か`外部結合`か考えながら、まずは最終的に欲しい情報である`students`テーブルと中間テーブルである `university_students`テーブルを結合して、`students`レコードを全件取得してみましょう。

※結合の構文は `inner join(left join)` `結合するテーブル名` `on` `結合先テーブル名`.`カラム名` = `結合するテーブル名`.`カラム名`

<details><summary>`name`が`UniversityA`のレコードを1件取得する</summary><div>

```sql
 select students.* from students inner join university_students on students.id = university_students.student_id;
```
</div></details>

取得できましたか？
これは`Rails`だと以下のようになります。

```ruby
Student.joins(:university_student)
```

#### 結合したテーブルを使って、`UniversityA`に紐づいた `students`レコードを全件取得する

では、次は本題である`UniversityA`に紐付いた`students`レコードを全件取得していきましょう。
先ほど`students`テーブルと`university_students`テーブルを結合したので、今回は追加で`university_students`テーブルと`universities`テーブルを結合します。

そして、`universities`レコードの`name`カラムが`UniversityA`であるものを特定すると、`UniversityA`に紐づいた `students`レコードを全件取得することができます。

`StudentA`〜`StudentF`まで取得できていたらOKです :thumbsup: 

<details><summary>`UniversityA`に紐づいた `students`レコードを全件取得する</summary><div>

```sql
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';
```
</div></details>

これを`Rails`で書くと以下のようになります。

```ruby
Student.joins(:university).where(universities: { name: 'UniversityA' })
```

この辺りから「`Rails`スゲー！！」という気持ちが大きくなってくるのではないでしょうか？
`joins`が`:university`のみの記述で良いのは、`Model（Student）`で `has_one :university, through: :university_student`を定義しているからです。

Railsは関連付けさえ綺麗に定義できれば上記のように記述をどんどん簡略化できるのでよいですね :thumbsup: 

#### `UniversityA`に所属している`StudentA`の`course_registrations`レコードを全件取得する

この辺から関連するテーブルが増えてきてごちゃごちゃします。
一つずつ紐解いて考えていきましょう。

ここでは `UniversityA`大学に所属している`StudentA`さんの`course_registration`レコード、つまり履修登録情報を取得します。

ここで簡単に仕様を説明します。
`course_registrations`レコードは年度毎に作成されます。

`StudentA`さんは、`2020年度`の時点で2回生です。
そのため、`course_registrations`レコードは2レコード作成されています。

`students`テーブルと`course_registrations`テーブルは`student_course_registrations`という中間テーブルを持っています。

<details><summary>`UniversityA`大学に所属している`StudentA`さんの`course_registration`レコードを全件取得する</summary><div>

```sql
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';
```
</div></details>

これを`Rails`で書くとこうなります。

```ruby
 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`という中間テーブルがいます。
では、今までの知識を活かして取得してみましょう！

<details><summary>`UniversityA`に所属している`StudentA`の `2020`年度の`CourseRegistration（履修登録情報）`に紐付いた`subjects`レコードを全件取得する</summary><div>

```sql
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';
```
</div></details>

これを`Rails`で書くと以下のようになります。

```ruby

Subject.joins(course_registration: { student: :university })
       .where(
         universities: { name: 'UniversityA' },
         students: { name: 'StudentA' },
         course_registrations: { year: '2020' }
       )
```

やっぱり`Rails`って凄いですね。。。

#### `UniversityA`に所属する`TeacherA`が受け持っている科目 `基礎英語` を `2020年度`に受講する生徒を全件取得する

`subjects`レコードを取得したから、今度はどうせ`teachers`レコードでも取得するんでしょうと思われた方もいるかもしれません。
ですが、恐らくワンパターンすぎて飽きてきたという方もいるでしょう。
私も流石に（ちょっともういいかな……）と思えてきたため、`teachers`レコードを追加するのは各自で試してみていただければ、と思います。

今度は生徒ではなく、教員側がデータベースに登録された情報を参照したい場合を考えてみます。

表題のようなレコードが欲しい、というケースですが、例えば `教員`が 「今年度の出席簿を作りたいな……」と思った時に、履修対象者を全件取得したい、といった時に起こり得そうですね。



<details><summary>`UniversityA`に所属する`TeacherA`が受け持っている科目 `基礎英語` を `2020年度`に受講する生徒を全件取得する</summary><div>

```sql
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';
```
</div></details>

これを`Rails`で書くと以下のようになります。

```ruby
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を書いてみましょう！

<details><summary>各生徒ごとの総取得単位を取得する</summary><div>

```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
```
</div></details>


これはRailsで書くと以下のようになります。

```ruby
 Student.joins(course_registrations: :subjects)
        .group('students.name')
        .sum(:credit)
```

:rolling_eyes:

書く量が全然違いますよね。
ただ、これを書こうと思った時に、結局どんな感じのSQLが発行されて欲しいのか？というところがわからないと、「？？？」となると思います。（私は毎回そうなっていました）

また、「上みたいな場合だと`Ruby`でなんとかできそうだから、`map`とか`each_with_object`使ってなんとかしちゃお」と私は思いがちだったのですが、純粋に値が必要なだけであればデータベースから直接取得できるので、`Ruby`でゴリ押すのではなく、いい感じの`SQL`を発行して必要な値を取得する、というのも必要なスキルだなと思いました :thumbsup: 


## 最後に

親子関係のあるテーブルならまだ良いのですが、親子孫曽孫……のような構成のテーブルがあった時に、「親から曽孫ってどうやってとるんだ！？！？」と混乱することが多かったのですが、「どのテーブルのレコードが主人公になっているのか？」を意識しながら書いていくとそんなに複雑ではないということが分かったと思います。

また、Railsでレコードを取得する時も少し混乱してしまいがちですが、SQLのテーブル結合を意識するとシンプルに書いていくことができるので、`こんなSQLが発行されて欲しい！`というのを意識しながら書いていくと良いですね :thumbsup: 

Railsでシンプルに書いていこうと思うと、Modelに定義する`関連付け`が大事だという話を少ししました。テーブルが複雑になればなるほど、この`関連付け`の定義も難しくなります（ここ最近私が頭を抱えているところです。）

次はRailsで条件付きの `has_one`・`has_many`の定義の仕方を学べるようなQiitaが書けたらと思っています :thinking: