多対多のソートをGROUP BYとGROP_CONCATで実現する

## はじめに
多対多のソートで手こずったので、備忘録として残します。

### やりたいこと
多対多で、もう片方のテーブル先の値で一覧をソートしたい。
​
大学生の履修登録アプリを例にします。
学生は複数の講義に登録でき、また講義も複数の学生を持つことができます。
​
次のER図より、「講義テーブル」と「学生テーブル」が「履修登録テーブル」によって接続されていることがわかります。

![](https://storage.googleapis.com/zenn-user-upload/c4a67bb598de-20231128.png )

教授から「自分の講義にはどの学生がくるっけなぁ...学生コード順に知りたいな」という要望があるとします。
そこで、講義テーブルの一覧を表示しつつ、講義ごとに学生コード順に並び替えたい時のクエリを考えます。

## 多対多の並び替えSQL

```sql
SELECT
    c.*,
    GROUP_CONCAT(s.student_code ORDER BY s.student_code SEPARATOR '/') AS コースを履修する学生　--　講義テーブルに紐づく学生コードの集合
FROM
    courses c
    LEFT JOIN
        student_course_enrollments sce -- 中間テーブルの結合
    ON  c.course_id = sce.course_id
    LEFT JOIN
        students s
    ON  sce.student_id = s.student_id
GROUP BY
    c.course_id　-- 講義ごとにグループ化
ORDER BY
    コースを履修する学生
;
```
​
結果
![](https://storage.googleapis.com/zenn-user-upload/6f975050a3db-20231128.png)
​
![スクリーンショット 2023-12-04 22.59.46.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1650886/05049ef6-1bad-2057-42c7-4a2d7724756a.png)

### 解説

ポイントはGROUP_CONCAT関数とクエリの実行順序です。

![スクリーンショット 2023-12-04 23.12.44.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1650886/c816dd4c-a7b1-d6bb-9aeb-9605d63a03ea.png)

クエリの実行順序はFROM→GROUP BY→SELECT→ORDER BYの順で実行されます。
まず、FROMでは講義テーブルを呼び出し、さらに講義テーブルに紐づく、履修登録テーブル、学生テーブルを結合します。
次にGROUP BYで講義IDの集合を作成します。FROMの段階で、履修登録テーブル、学生テーブルが結合しているため、講義IDの集合に合わせて値が仕分けされます。
SELECTでは、GROUP_CONCATを使って、講義IDの集合に対して紐づく値をまとめています。
まとめた値をORDER BYで並び替えをしています。


#### GROUP_CONCAT
```sql
GROUP_CONCAT(s.student_code ORDER BY s.student_code SEPARATOR '/') AS コースを履修する学生
```
`GROUP_CONCATは`MySQLに用意されている集計関数の一種で、`GROUP BY`で同じデータを集約した時に、NULL以外の値を含む文字列を連結して返してくれます。また集合が存在しない場合NULLを返します。
​
`SEPARATOR`により、各講義に登録されている学生のstudent_codeを'/'で区切って連結します。
​
### GROUP BY
```sql
GROUP BY c.course_id
```
講義ごとにグループ化します。これにより、各コースに対する学生リストを集約できます。
​
### ORDER BY
```sql
ORDER BY
    コースを履修する学生
```
SELECT文で「コースを履修する学生カラム」を作成したので、`/`を含んだ文字列で並び替えができます。
（'S001/S002'の状態で並び替えしています。）
​
## まとめ
​
多対多の結合先での並び替えクエリを記載しました。
何かの参考になれば幸いです。
​

### 参考
https://dev.mysql.com/doc/refman/8.0/ja/aggregate-functions.html#function_group-concat

​
### おまけ
<details><summary>ER図のPluntUML記法</summary>

```
@startuml
' hide the spot
hide circle
​
' avoid problems with angled crows feet
skinparam linetype ortho
​
entity "履修登録" as e01 {
  *e1_id : number <<generated>>
  --
  *name : text
  description : text
}
​
entity "学生" as e02 {
  student_id : int <<PK>>
  --
  name : varchar
  student_code: varchar
}
​
entity "コース" as e03 {
  course_id : number <<PK>>
  --
  course_name : varchar
  description: text
}
​
e01 |o..o{ e02
e01 |o..o{ e03
@enduml
@enduml
```
</details>
​

<details><summary>SQL</summary>


```sql
-- テーブル作成
CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100),
    student_code VARCHAR(20)
);
CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100),
    description text,
    credits INT
);
CREATE TABLE student_course_enrollments (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);
​
-- ダミーデータ
INSERT INTO students (student_id, name, student_code) VALUES (1, '佐藤 健', 'S001');
INSERT INTO students (student_id, name, student_code) VALUES (2, '鈴木 花子', 'S002');
INSERT INTO students (student_id, name, student_code) VALUES (3, '高橋 一郎', 'S003');
INSERT INTO students (student_id, name, student_code) VALUES (4, '田中 美咲', 'S004');
INSERT INTO students (student_id, name, student_code) VALUES (5, '伊藤 太郎', 'S005');
INSERT INTO students (student_id, name, student_code) VALUES (6, '山本 あゆみ', 'S006');
INSERT INTO students (student_id, name, student_code) VALUES (7, '中村 陽子', 'S007');
INSERT INTO students (student_id, name, student_code) VALUES (8, '小林 翔太', 'S008');
INSERT INTO students (student_id, name, student_code) VALUES (9, '加藤 絵理', 'S009');
INSERT INTO students (student_id, name, student_code) VALUES (10, '吉田 真也', 'S010');
​
​
INSERT INTO courses (course_id, course_name, description) VALUES (1, '総合演習II', '実践的なプロジェクトを通じて、ソフトウェア開発の全体的な流れを学びます。');
INSERT INTO courses (course_id, course_name, description) VALUES (2, 'Java基礎', 'Java言語の基礎から応用までを学び、オブジェクト指向プログラミングの概念を理解します。');
INSERT INTO courses (course_id, course_name, description) VALUES (3, 'オペレーティングシステム', '現代のOSの構造と機能、プロセス管理などを学び、実践的な知識を身に付けます。');
INSERT INTO courses (course_id, course_name, description) VALUES (4, 'ウェブ技術の基礎', 'HTML、CSS、JavaScriptを学び、基本的なウェブサイトを作成する方法を習得します。');
INSERT INTO courses (course_id, course_name, description) VALUES (5, 'セキュリティ基礎', '情報セキュリティの基本原則と脆弱性について学び、対策技術について理解を深めます。');
​
​
INSERT INTO student_course_enrollments (student_id, course_id) VALUES (1, 1);
INSERT INTO student_course_enrollments (student_id, course_id) VALUES (2,1);
INSERT INTO student_course_enrollments (student_id, course_id) VALUES (1, 2);
INSERT INTO student_course_enrollments (student_id, course_id) VALUES (3, 2);
INSERT INTO student_course_enrollments (student_id, course_id) VALUES (4, 2);
INSERT INTO student_course_enrollments (student_id, course_id) VALUES (5, 2);
INSERT INTO student_course_enrollments (student_id, course_id) VALUES (1, 3);
INSERT INTO student_course_enrollments (student_id, course_id) VALUES (2, 3);
INSERT INTO student_course_enrollments (student_id, course_id) VALUES (3, 3);
INSERT INTO student_course_enrollments (student_id, course_id) VALUES (4, 3);
```
</details>

​