MySQLで、動的にレコードを参照できますか？

Question

実現したいこと

MySQLで、動的にDB内を参照するようなDB構成、あるいはSQL文は作成できますか？

例えば、以下のようなDB構成にしたいです。

ID	製品名	原料1	原料2
1	製品A	原料x	原料y
2	製品B	製品A	原料z
3	製品C	製品B	原料n

このとき、製品Bを取り出したときに、原料に含まれる製品Aのレコードを参照し、出力結果として「原料x,原料y,原料z」という結果が得たいです。
理由として、製品Aの原料が変わる可能性があるため、原料をベタ書きすることを避けたいです。
また、多重で参照する場合もあり得、製品Cを取り出したときには、結果として「原料x,原料y,原料z,原料n」が得たいです。
出力結果は、多次元配列でも問題ありません。製品Cを取り出したときには、結果として[[[原料x,原料y],原料z],原料n]でもOKです。

最終的にPHP（PDO）で扱いたいと考えています。

そもそもこういったことが実現可能なのかどうかが、調べた限りだと皆目見当がつかなかったため、質問させていただいた次第です。
どうぞよろしくお願いいたします。

Answer 1

テーブルを分割して「製品・原料マスタ」と、「製品構成マスタ」にします。

ID	製品・原料名
1	製品A
2	製品B
3	製品C
4	原料x
5	原料y
6	原料z
7	原料n

製品ID	連番	原料ID
1	1	4
1	2	5
2	1	1
2	2	6
3	1	2
3	2	7
4	1	null
5	1	null
6	1	null
7	1	null

※原料IDがnullはすなわち、この製品自体が原料であることを示します

こうすれば、ある製品が3以上の素材から作られる場合でも対応可能ですし。

ただ、「ある製品を構成するすべての原料」を列挙する際には、階層問い合わせを行わねばなりませんが、MySQL は 8.0 以前では対応していません。
その場合はプログラムで親から順にたどっていくなどする必要があります。

Comments

[TsunakiM]

ご回答ありがとうございます。
ご提示の方法にヒントを得、原料に別の製品を使っているかどうかのカラムを作成します。
それを元に、PHP側で再検索をかけるか否かを処理すればいけそうです。

もしご存知でしたら、負荷量の相談なのですが、上記の方法を取るとして
　・レコードの総数は約500
　・一度の操作において、再問い合わせでDBを参照する回数は最大で60回程度
　・同時に操作する人数は10人程度。どんなに多くても100人は超えない
以上の条件では、常識的な処理量でしょうか？
（一般的にみて、明らかに過剰な処理量でなければ問題ないと考えています）

[tacsheaven]

全然常識的な処理量ですが、レコード数が500程度なら、全件取ってきて配列に格納しておいて、PHP で再帰的に処理してもよいような気がします。

Answer 2

目的としては階層構造なので、ベタ打ちしたくないのであれば、経路列挙モデルではなく入れ子集合モデルということになります。

MySQLが8.0なら再帰クエリーが使えますが、そうでないならSQLには手間や制約（階層の深さ）が掛かりますが実現できないわけではありません。

phpで再帰を行うのはレスポンスやメモリなどの問題が生じるでしょうから、DB側での処理とした方が良いでしょう。

SQLで木と階層構造のデータを扱う（１）―― 入れ子集合モデル
階層構造(入れ子集合モデル)について

＜例＞

製品

ID	名称
1	製品A
2	製品B
3	製品C

原料

ID	名称
1	原料n
2	原料z

構成
|ID |親ID|構成ID|製品ID|原料ID
|:--|:--:|:--:|--:|
|1 |0| ||1
|2 |0| ||2
|3 |0| |1|
|4 |3|1|||
|5 |3|2|||
|6 |0| |2|
|7 |6|3|||
|8 |6|2|||
|9 |0| |3|
|10 |9|6|||
|11 |9|1|||

下位ノードへの問い合わせは自己結合となり、COALESCE(A.構成ID, A.ID)=B.親IDとすることで、製品の展開が行える事となる。
※上記は構成を示すことを主としいて、実際には、原料や商品といったマスタを別に持ち、外部参照する方が良いと思います。
↑イメージ合わせました

Comments

[yambejp]

部品表の場合、データ構造として子に対して親が一つではないので入れ子にできなくないでしょうか？
（入れ子にすると同じ部品が違うものとして管理される？）

[sazi]

入れ子モデルでは製品や原料は重複する事になるので、同じキーだけでは管理できません。
テーブルレイアウトを考えてみました。

[TsunakiM]

ご回答ありがとうございます。
今回はMySQL5.7を利用するため、ご提示の方法はとれなさそうです。
ただ、再帰クエリという概念を知らなかったため、大変参考になりました。

[sazi]

再帰クエリは手段です。階層問い合わせは再帰クエリの別解です。
案の構造でも階層問い合わせで可能ですし、tacsheavenさんの提案されている構造も入れ子ですから、結局のところ基本は同じです。
私の案は再帰での問い合わせを意識したものなので連番等を生成しなくて済む分若干シンプルだと思いますが。

Answer 3

create table tbl(pid varchar(10),cid varchar(10));
insert into tbl values('A','X'),('A','Y'),('B','A'),('B','Z'),('C','B'),('C','N');

という状態だとして、仮にDが

insert into tbl values('D','A'),('D','B'),('D','X'),('D','O');

だとする場合は存在するのでしょうか？
※AはBを部品とするが、DはAとBを部品とする

また、Dが仮に少量の自分自身を材料に使うなど循環する可能性はあるのでしょうか？

実際のところ、適当な階層を想定してleft joinするだけで処理はできますが
上記のような特殊な状況によってはデータのダブリがでてきたりループが発生したりします

• 仮SQL

select * from tbl t1
left join tbl t2 on t1.cid=t2.pid
left join tbl t3 on t2.cid=t3.pid
left join tbl t4 on t3.cid=t4.pid
left join tbl t5 on t4.cid=t5.pid
left join tbl t6 on t5.cid=t6.pid
left join tbl t7 on t6.cid=t7.pid
left join tbl t8 on t7.cid=t8.pid
left join tbl t9 on t8.cid=t9.pid

これを元に

select t1.pid
,concat_ws(',',
group_concat(distinct t1.cid)
,group_concat(distinct t2.cid)
,group_concat(distinct t3.cid)
,group_concat(distinct t4.cid)
,group_concat(distinct t5.cid)
,group_concat(distinct t6.cid)
,group_concat(distinct t7.cid)
,group_concat(distinct t8.cid)
,group_concat(distinct t9.cid)
) as cidx
from tbl t1
left join tbl t2 on t1.cid=t2.pid
left join tbl t3 on t2.cid=t3.pid
left join tbl t4 on t3.cid=t4.pid
left join tbl t5 on t4.cid=t5.pid
left join tbl t6 on t5.cid=t6.pid
left join tbl t7 on t6.cid=t7.pid
left join tbl t8 on t7.cid=t8.pid
left join tbl t9 on t8.cid=t9.pid
group by pid

上記もDのように使用部品の競合があるとユニークに拾えないので別処理が必要になります

procedureで処理

久しぶりに真面目に書いてみました

• 元データ

create table tbl(pname varchar(10),cname varchar(10));
insert into tbl values('A','X'),('A','Y'),('B','A'),('B','Z'),('C','B'),('C','N');

• 追加データ

insert into tbl values('D','A'),('D','B'),('D','X'),('D','O');

• tbl

pname	cname
A	X
A	Y
B	A
B	Z
C	B
C	N
D	A
D	B
D	X
D	O

• ツリーテーブル作成

create table tree (id int NOT NULL primary key auto_increment,pid INT null,cname varchar(64),level int NOT NULL default 0,l int not null default 0,r int not null default 0);

• procedure作成

drop procedure if exists set_tree_table;
delimiter //
create procedure set_tree_table()
begin 
declare count int default 0;
declare count_stop int default 5;
declare lastid int default 0;
declare done int default 0;
declare a int default 0;
declare cur cursor for
select id from tree where level=0 order by pid asc,id desc;
truncate tree;
insert into tree(cname,level)
select distinct pname,1 from tbl;
insert into tree(pid,cname,level)
select t2.id,t1.cname,2 from tbl as t1
inner join tree as t2 on t1.pname=t2.cname;
while count<count_stop and done=0 do
insert into tree(pid,cname,level)
select distinct t1.id,t3.cname,(count+3) from tree as t1
inner join tree as t2 on t1.cname=t2.cname and  t1.level=(count+2) and t2.level=(count+1)
inner join tree as t3 on t2.id=t3.pid;
select @lastid:=LAST_INSERT_ID();
set count=count+1;
if lastid=@lastid then
set done=1;
else 
set lastid=@lastid;
end if;
end while;
end
//
delimiter ;

• procedure実行

tblのデータを追加、削除、更新したら都度procudureを実行します

call set_tree_table;

• treeテーブル

id	pid	cname	level	l	r
1	NULL	A	1	1	6
2	NULL	B	1	7	16
3	NULL	C	1	17	30
4	NULL	D	1	31	52
8	1	X	2	2	3
9	1	Y	2	4	5
10	2	A	2	8	13
11	2	Z	2	14	15
12	3	B	2	18	27
13	3	N	2	28	29
14	4	A	2	32	37
15	4	B	2	38	47
16	4	X	2	48	49
17	4	O	2	50	51
23	10	X	3	9	10
24	14	X	3	33	34
25	10	Y	3	11	12
26	14	Y	3	35	36
27	12	A	3	19	24
28	15	A	3	39	44
29	12	Z	3	25	26
30	15	Z	3	45	46
38	27	X	4	20	21
39	28	X	4	40	41
40	27	Y	4	22	23
41	28	Y	4	42	43

具体的なデータ

• 全構成要素を表示

select t1.cname,group_concat(distinct t2.cname) as all_child from tree as t1
inner join tree as t2 on t2.l > t1.l and t2.l < t1.r
where t1.pid is null 
group by cname;

• 結果

cname	all_child
A	Y,X
B	X,Y,Z,A
C	B,A,Z,X,Y,N
D	Y,A,X,B,O,Z

• 階層状況を視覚化

select concat(repeat('__', level -1 ) , cname ) AS cname
FROM tree
order by l;

• 結果

A
__X
__Y
B
__A
____X
____Y
__Z
C
__B
____A
______X
______Y
____Z
__N
D
__A
____X
____Y
__B
____A
______X
______Y
____Z
__X
__O

Comments

[TsunakiM]

ご回答ありがとうございます。
現状では、製品X＝製品Y+製品Zのような状況はありえます。
一方、製品Xの原料として、製品X自身を使うことはまずありえないのですが、100%絶対にないとは言い切れない状況です。
そのため、ご提示の方法ですと危険があるため、今回はとれなさそうです。

ただ、JOINの使い方として大変勉強になるものでした。機会があればこの形式のSQL文を使ってみようと思います。

[yambejp]

久しぶりにやる気をだしてみました
考え方はsaziさんの提案のとおりです