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

  • 2021年7月22日
  • 2021年7月23日
  • SQL

 想定する状況としては次の様な 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;
1..n:1 検索結果
member_id email 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 の場合次の様に複数の所属の名前を一マスに詰め込んだ結果が欲しいとします。

n:n 検索結果
member_id email 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;
1..n:1 (右の1は結合した名前を持つview)検索結果
member_id email 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 本社
浜松店
静岡店
>株式会社シーポイントラボ

株式会社シーポイントラボ

TEL:053-543-9889
営業時間:9:00~18:00(月〜金)
住所:〒432-8003
   静岡県浜松市中央区和地山3-1-7
   浜松イノベーションキューブ 315
※ご来社の際はインターホンで「316」をお呼びください

CTR IMG