想定する状況としては次の様な 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;
member_id | name | |
---|---|---|
839 | zyamada@example.net | 本社 |
953 | ztakahashi@example.net | 浜松店 |
725 | zmurayama@example.org | 浜松店 |
429 | zkondo@example.com | 静岡店 |
904 | zkoizumi@example.com | 本社 |
980 | zkiriyama@example.net | 浜松店 |
n:n の場合次の様に複数の所属の名前を一マスに詰め込んだ結果が欲しいとします。
member_id | name | |
---|---|---|
839 | zyamada@example.net | 本社 浜松店 |
953 | ztakahashi@example.net | 浜松店 |
725 | zmurayama@example.org | 浜松店 静岡店 |
429 | zkondo@example.com | 静岡店 |
904 | zkoizumi@example.com | 本社 |
980 | zkiriyama@example.net | 本社 浜松店 静岡店 |
もともとその様な構造のデータを取り扱うと分かっていたならばアプリケーションプログラム上のリレーショナルデータベースのテーブルをクラスに写す機能にまずあるリレーションを使うのが楽です。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_id | name |
---|---|
1 | 本社 浜松店 |
2 | 浜松店 |
3 | 浜松店 静岡店 |
4 | 静岡店 |
5 | 本社 |
6 | 本社 浜松店 静岡店 |
目的のそれに近いです。この表との結合を旧来の 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;
member_id | name | |
---|---|---|
839 | zyamada@example.net | 本社 浜松店 |
953 | ztakahashi@example.net | 浜松店 |
725 | zmurayama@example.org | 浜松店 静岡店 |
429 | zkondo@example.com | 静岡店 |
904 | zkoizumi@example.com | 本社 |
980 | zkiriyama@example.net | 本社 浜松店 静岡店 |