【SQL】GROUP_CONCAT で元々 1..n:1 だった読み込みクエリ群を楽に n:n 対応に変える

　想定する状況としては次の様な 1..n:1 のリレーションで作られていたデータベースを

　次の様に中間テーブルをはさむ n:n に変えたいという要望への対応です。

　今まで所属は常に一つだったが掛け持ちもあり得る様になる、とかそういった具合です。こういった変更はシステムのいたるところに手を加える必要がままあります。所属が主役のシステムならそれはもう大変です。そういった変更の中に、今まで次の様に読み込んで、表示していた検索結果を n:n 対応の検索結果に変える、という変更がままあります。
　例えば 1..n;1 の場合、次のように検索と結果を表示して、

# 検索用 SQL
select member_id, members.email, member_groups.name
from members
         left join member_groups
                   on members.member_group_id = member_groups.member_group_id
order by email desc;

　n:n の場合次の様に複数の所属の名前を一マスに詰め込んだ結果が欲しいとします。

　もともとその様な構造のデータを取り扱うと分かっていたならばアプリケーションプログラム上のリレーショナルデータベースのテーブルをクラスに写す機能にまずあるリレーションを使うのが楽です。PHP のフレームワークの Laravel ならば例えば次のようにできます。

class SomeController
    public function search()
    {
	    return Member::with('groups')->get()// 実際は get の代わりに検索条件等を渡せる検索クエリ
		    ->map(static function(Member $m){
		        $m->groupName = $m->groups
                            ->map(fn($g) => $g->name)
                            ->join("\n");
		        return $m;
		    });
    }
}

Eloquent：リレーション 8.x Laravel
　N+1問題対策こそ必要ですが、これだけで複数の所属の名前を一会員に紐づけられ、紐づけ済みの結果を返せます。

　しかしながら元々一つだけしかつながりがない場合、直接呼出しの記述が簡易になるため次のような記述が様々な場所に現れやすいです。

# 検索用 SQL
select member_id, members.email, member_groups.name
from members
         left join member_groups
                   on members.member_group_id = member_groups.member_group_id
order by email desc;

# データベースのリレーションをプロパティに写した呼出し
$groupName = $member->group->name;

　こうなると該当箇所を探し回ってちまちまやるか、正規表現等で該当箇所をまとめて置換するかが必須になります。この時に最初からそうであった場合で作った処理を当てはめるのはいささか手間なパターンが多くなります（いい感じにメソッドなり関数に回せないと無意味なコピペコードが量産され、後の改修が更に手間になります）。
　もともと一テーブルから読みだしていたものなので同じく一テーブルから所属の名前をまとめて呼び出せるようにするのが楽です。まとめて呼び出すSQLは次で実現できます。

SELECT
   member_id,
   GROUP_CONCAT(member_groups.name SEPARATOR '\n' ) AS name 
FROM
   member_and_member_group_relations 
   LEFT JOIN
      member_groups 
      ON member_and_member_group_relations.member_group_id = member_groups.member_group_id 
GROUP BY
   member_id;

　この結果出来上がる表は次のようなものになります。

　目的のそれに近いです。この表との結合を旧来の member_groups との結合の代わりに用います。この表の name は GROUP_CONCAT 関数で作っています。
MySQL ：： MySQL 8.0 リファレンスマニュアル ：： 12.20.1 集計関数の説明#GROUP_CONCAT
　これは集計関数の一つで GROUP_CONCAT により GROUP BY でのまとめ方に文字列結合を使えます。例では簡易ですが、次のように結合の順番も指定できます。

GROUP_CONCAT(member_groups.name ORDER BY display_order SEPARATOR '\n' ) AS group_name

　そしてこの表を次のように view にします。

CREATE VIEW member_group_names AS 
SELECT
   member_id,
   GROUP_CONCAT(member_groups.name SEPARATOR '\n' ) AS name 
FROM
   member_and_member_group_relations 
   LEFT JOIN
      member_groups 
      ON member_and_member_group_relations.member_group_id = member_groups.member_group_id 
GROUP BY
   member_id;

　こうすると今まで所属名を呼ぶために member_groups を呼んでいたところを次のように member_groups から member_group_names の結合に置き換えるだけで、要望を叶えられます。

# 検索用 SQL
select members.member_id, members.email, member_group_names.name
from members
         left join member_group_names
                   on members.member_id = member_group_names.member_id
order by email desc;