カンマ区切りのデータのリストをマスタテーブルにある日本語に変換して表示させたいのですが…。

Question

現在、あるシステム管理ツールをlaravelで作成しています。
今、下のようなシステムテーブルの内容を一覧で表示する機能を作成しています。

ID	NAME	creator_id
1	TEST1	A0001
2	TEST2	A0002,A0003
3	TEST3	A0004

テーブルのカラム「creator_id」には、システムの管理担当者の社員番号が複数入るようになっていて、
「creator_id」は、社員マスタに紐づくようになっています。

ID	CODE	NAME
1	A001	テスト太郎
2	A002	テスト次郎
3	A003	テスト三郎
4	A004	テスト花子

今回やりたいことは、システムテーブルの「creator_id」を社員マスタの「NAME」に置き換えて、下のような一覧で表示させたいことです。

ID	NAME	creatorname
1	TEST1	テスト太郎
2	TEST2	テスト次郎、テスト三郎
3	TEST3	テスト花子

SQLについては、このページを参考に作成し、実際に想定通りの結果を取得できました。

SELECT
  S.ID,
  S.NAME,
  group_concat(M.NAME SEPARATOR '、') AS creatorname
FROM システム AS S
  LEFT JOIN 社員マスタ AS M ON S.creator_id REGEXP CONCAT("(^|,)", M.CODE, "(,|$)")
GROUP BY S.ID;

ただ、このSQLをlaravelにどうやって組み込めばいいのかわからず、作業が止まっています。
初心者のため、クエリビルダの使い方が良くわからないため、ご教示いただければと思います。

よろしくお願いします。

Comments

[退会済みユーザー]

一つのカラムにカンマ区切りでデータを突っ込むこと自体を変更することはできない事情がおありですか？

Answer 1

creator_idを別テーブルに正規化すれば簡単に処理できますが
いまのままではSQLで処理するには非効率的すぎると思います

sample

• 元テーブル

create table tbl1(ID int unique,NAME varchar(10));
insert into tbl1 values(1,'TEST1'),(2,'TEST2'),(3,'TEST3');
create table tbl2(ID int unique,uid int,creator_id varchar(10),unique(uid,creator_id));
insert into tbl2 values(1,1,'A0001'),(2,2,'A0002'),(3,2,'A0003'),(4,3,'A0004');
create table tbl3(ID int unique,CODE varchar(10) unique,NAME varchar(10));
insert into tbl3 values(1,'A0001','テスト太郎'),(2,'A0002','テスト次郎'),(3,'A0003','テスト三郎'),(4,'A0004','テスト花子');

• 集計1

select t1.ID,group_concat(distinct t.1NAME) as NAME,group_concat(creator_id) as creator_id
from tbl1 as t1
inner join tbl2 as t2 on t1.ID=t2.uid
group by ID;

• 集計2

select t1.ID,group_concat(distinct t1.NAME) as NAME,group_concat(t3.NAME) as creator_name
from tbl1 as t1
inner join tbl2 as t2 on t1.ID=t2.uid
inner join tbl3 as t3 on t2.creator_id=t3.CODE
group by ID;

Comments

[kmk935]

ご回答ありがとうございます。
また返信遅くなりまして申し訳ございません。

おっしゃる通り、「creator_id」で新しくテーブルを作って正規化した方が処理も簡単に済みそうです。
ありがとうございます、これでやってみます