質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.01%

RDB(mysql)からリストメニュー。入れ子集合モデルを作る前段階の隣接リストモデル作成

解決済

回答 3

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 879

daisaku64

score 12

実現したいこと

下記の products_table を読み込み、その下にある convert.sql をsqlまたはphpにて書き出したいのですが、どのたかヒントをください。
ちなみに「接続リストモデル」とは、レコードに親 ID を持たせて親子関係を表現するモデルのことです(※重要)
他のサイトにSQLを使ってSQLを生成するということを実際行っている達人がいらっしゃったので、いろいろ試してはいるのですがうまくいきません。
SQLの詳しい方ご教授ください。よろしくお願い致します。

Mysql は5.6、PHP は5.4です

SQL初心者

タグ #SQL #データベース #木構造 #入れ子集合モデル #RDB

用意したテーブル(products_tbl)

hs_id ct_01 ct_02 ct_03 ct_04 ct_05 ct_06 name URL
1001 A AA AAA A-ct4 name1001 http://www
1002 A AA BBB A-ct4 name1002 http://www
1003 A AA BBB B-ct4 name1003 http://www
1004 A AA CCC A-ct4 name1004 http://www
1005 A BB BBB A-ct4 name1005 http://www
1006 A BB BBB A-ct4 name1006 http://www
1007 A BB CCC B-ct4 name1007 http://www
1008 B AA AAA A-ct4 A-ct5 A-ct6 name1008 http://www
1009 B BB AAA B-ct4 B-ct5 name1009 http://www
1010 B BB BBB A-ct4 C-ct5 name1010 http://www
create table tbl(
id int not null unique,
parent_id int null,
name varchar(30),
url varchar(100),
link tinyint,
level int not null default 0,
l int null,
r int null
);

作りたいsql(convert.sql)親子関係をparent_idに

insert into tbl(id,parent_id,name,url,link) values
( 1,null,'A','#',1),
( 2,null,'B','#',1),
( 3,1,'AA','#',1),
( 4,1,'BB','#',1),
( 5,2,'AA','#',1),
( 6,2,'BB','#',1),
( 7,3,'AAA','#',1),
( 8,3,'BBB','#',1),
( 9,3,'CCC','#',1),
(10,4,'BBB','#',1),
(11,4,'CCC','#',1),
(12,5,'AAA','#',1),
(13,6,'AAA','#',1),
(14,6,'BBB','#',1),
(15,7,'A-ct4','#',1),
(16,8,'A-ct4','#',1),
(17,8,'B-ct4','#',1),
(18,9,'A-ct4','#',1),
(19,10,'A-ct4','#',1),
(20,11,'B-ct4','#',1),
(21,12,'A-ct4','#',1),
(22,13,'B-ct4','#',1),
(23,14,'A-ct4','#',1),
(24,21,'A-ct5','#',1),
(25,22,'B-ct5','#',1),
(26,23,'C-ct5','#',1),
(27,24,'A-ct6','#',1),
(28,15,'name1001','www',1),
(29,16,'name1002','www',1),
(30,17,'name1003','www',1),
(31,18,'name1004','www',1),
(32,19,'name1005','www',1),
(33,19,'name1006','www',1),
(34,20,'name1007','www',1),
(35,27,'name1008','www',1),
(36,25,'name1009','www',1),
(37,26,'name1010','www',1);

試したこと

SQLの「COUNT」と「GROUP BY」を使い結果を連結

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • SVC34

    2017/08/06 16:52

    要件や制約を文章で示してください

    キャンセル

  • daisaku64

    2017/08/06 17:19 編集

    Mysql は5.6、PHP は5.4です
    かなり難しい内容です。

    キャンセル

  • daisaku64

    2017/08/07 16:59

    SVC34さん いろいろと説明不足でごめんなさい。30年ぶりのプログラミングなもので。次は気をつけます。

    キャンセル

回答 3

checkベストアンサー

+1

これは前回さらっとながした経路列挙モデルを利用します。
実際ご提示されたサンプルが中途半端な経路列挙モデルなので連携がとれていません
以下テストしてみて下さい

  • productsテーブル
create table products_tbl(
hs_id int,ct_01 varchar(30),ct_02 varchar(30),ct_03 varchar(30),ct_04 varchar(30),ct_05 varchar(30),ct_06 varchar(30),name varchar(30),URL varchar(200));
insert into products_tbl values
(1001,'A','AA','AAA','A-ct4','','','name1001','http://www'),
(1002,'A','AA','BBB','A-ct4','','','name1002','http://www'),
(1003,'A','AA','BBB','B-ct4','','','name1003','http://www'),
(1004,'A','AA','CCC','A-ct4','','','name1004','http://www'),
(1005,'A','BB','BBB','A-ct4','','','name1005','http://www'),
(1006,'A','BB','BBB','A-ct4','','','name1006','http://www'),
(1007,'A','BB','CCC','B-ct4','','','name1007','http://www'),
(1008,'B','AA','AAA','A-ct4','A-ct5','A-ct6','name1008','http://www'),
(1009,'B','BB','AAA','B-ct4','B-ct5','','name1009','http://www'),
(1010,'B','BB','BBB','A-ct4','C-ct5','','name1010','http://www');


経路列挙モデルテーブルを作ります

create table keiro_tbl( pid int null,id int not null unique auto_increment,keiro varchar(255),name varchar(30),url varchar(255));


今回最大6階層+アイテムなので7回インサートします

insert into keiro_tbl(keiro,name,url)
select distinct concat('/',ct_01,'/') ,ct_01,'#' from products_tbl where ct_01!='';
insert into keiro_tbl(keiro,name,url)
select distinct concat('/',ct_01,'/',ct_02,'/') ,ct_02,'#' from products_tbl where ct_02!='';
insert into keiro_tbl(keiro,name,url)
select distinct concat('/',ct_01,'/',ct_02,'/',ct_03,'/') ,ct_03,'#' from products_tbl where ct_03!='';
insert into keiro_tbl(keiro,name,url)
select distinct concat('/',ct_01,'/',ct_02,'/',ct_03,'/',ct_04,'/') ,ct_04,'#' from products_tbl where ct_04!='';
insert into keiro_tbl(keiro,name,url)
select distinct concat('/',ct_01,'/',ct_02,'/',ct_03,'/',ct_04,'/',ct_05,'/') ,ct_05,'#' from products_tbl where ct_05!='';
insert into keiro_tbl(keiro,name,url)
select distinct concat('/',ct_01,'/',ct_02,'/',ct_03,'/',ct_04,'/',ct_05,'/',ct_06,'/') ,ct_06,'#' from products_tbl where ct_06!='';
insert into keiro_tbl(keiro,name,url)
select distinct concat('/',ct_01,if(ct_02!='','/',''),ct_02,if(ct_03!='','/',''),ct_03,if(ct_04!='','/',''),ct_04,if(ct_05!='','/',''),ct_05,if(ct_06!='','/',''),ct_06,'/',name,'/') ,name,url from products_tbl;


上記親のID(pid)がついてないので、updateします。

update keiro_tbl as t1,keiro_tbl as t2
set t1.pid=t2.id where t1.keiro=concat(t2.keiro,t1.name,'/')
  • 本チャンテーブル
create table tbl(
id int not null unique,
parent_id int null,
name varchar(30),
url varchar(100),
link tinyint,
level int not null default 0,
l int null,
r int null
);

流し込み

insert into tbl(id,parent_id,name,url,link)
select id,pid,name,url,1 from keiro_tbl

※経路カラムやurlカラムは長くなりがちなのでvarcharで収まらない場合は
適当なテキスト系の型を設定して下さい

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/08/07 11:06

    yambejpさん
    前回は大変勉強になりました。
    まずはソース読んでみます、ホント助かります
    ありがとうございます。感謝感謝

    キャンセル

  • 2017/08/07 11:14

    前回あえて言及していませんが、入れ子集合モデルについては
    以下がわかりやすいかと思います

    http://www.geocities.jp/mickindex/database/db_tree_ns.html

    私の考え方もほぼここの丸パクリです

    キャンセル

  • 2017/08/07 12:46

    私もこのページで入れ子集合モデルについて納得しました。
    でもなかなか応用できませんでww
    頑張ります。まだyambejpさんのソース解読中です。

    キャンセル

  • 2017/08/07 15:50

    経路列挙モデルテーブル
    この考え方すごいです!このテーブルのおかげで親IDが2行のSQLで取得できるんですね〜
    すごい!関心しました!

    1)商品データをインサートする
    2)経路列挙モデルテーブルを作ります
    3)経路列挙モデルテーブルにデータをインサート
    4)親IDを更新する
    5)隣接リストモデルテーブルを作ります
    6)隣接リストモデルテーブルに流し込みします
    7)コンバート用プロシジャーを実行
    8)入れ子集合モデル完成(親子関係のリスト完成)

    綺麗にWEB表示できました! 今回の一連のソース大事に勉強します
    本当にありがとうございました。プログラミングも30年も経つと忘れるものですね
    若い人たちに負けないように再び勉強します。

    感謝。感謝。

    キャンセル

+1

tblのCREATE TABLE文も少し変えてあります。
「COUNT」の使い所が謎ですが、
linkがcountの値を入れるなら、適宜書き換えてください。

以下のSQLでtbl.idの6まで登録可能です。
続きは考えてみてください。

create table tbl(
id int not null auto_increment unique,
parent_id int null,
name varchar(30),
url varchar(100),
link tinyint not null default 1,
level int not null default 0,
l int null,
r int null
);


insert into tbl
(
        parent_id
    ,    name
    ,    url
)
SELECT    null
    ,    T1.ct_01
    ,    '#'
FROM    products_tbl T1
GROUP BY T1.ct_01
;


insert into tbl
(
        parent_id
    ,    name
    ,    url
)
SELECT    (
            SELECT    id
            FROM    tbl T2
            WHERE    T2.parent_id    IS NULL
                AND    T2.name            = T1.ct_01
                AND    T2.url            = '#'
        )
    ,    T1.ct_02
    ,    '#'
FROM    products_tbl T1
GROUP BY T1.ct_01
    ,    T1.ct_02
;

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/08/07 08:29

    ありがとうございます。
    教えていただいたsql勉強してみます。
    テストしたら確かに6行出来上がっています。
    本番のデータは1000件弱ありますので対応できるか判断してみます

    キャンセル

  • 2017/08/07 10:45

    読んでみました。これは単純にproducts_tblを見ながらtblに書き出してるだけですよね?
    products_tblは変更・削除などが頻繁にあるため自動化したいだけなんです。
    すみません。まだ実現したいことが伝わっていないようで・・・

    SQLやPHPを使い「convert.sql」というSQL文を自動的に生成したいのです。(入れ子集合モデルを作るため)
    そして生成されたSQLを「product_tbl」に変更があったときに自動的に起動するという流れです

    キャンセル

  • 2017/08/07 11:51

    続きを考えて、id37まで登録するSQLはできましたか?

    とりあえず、どーしてもinsert文のsqlファイルを作成したいのであれば、1000行程度のデータであれば、毎回、全データを一時テーブルに格納し直して、一時テーブルからconcat句とinto句等を使って、sqlファイルを出力したりしても良いかと。

    ちなみに、流れ的に、変更があったときに、わざわざ事前に作成したsqlファイルを実行するという事のであれば、
    変更があったときに、product_tblを変更する前にtblにデータを登録しても良いのでは?とは思います。

    キャンセル

  • 2017/08/07 15:53

    tomari_performさん
    このたびは説明がかなり不足してご迷惑をおかけしました。
    30年ぶりのプログラミングで思うように説明できませんでした
    また何かありましたらお力をお貸しください。
    ありがとうございました。

    キャンセル

0

SQLを使ってSQLを書き出すということを実際行っている達人
convert.sql

⇒目的は分かりませんが、convert.sqlで記述された内容を出力するくらいなら、
INSERT INTO tbl(~~) SELECT~
の方が効率は良いです。

挿入先のテーブルのidを使いまわしているため、
1つのSQLで作るのはかなり難しい(というより面倒&大変。不可能ではない)ですが、
複数回(7回くらい)、SQLを実行して作成する事をおすすめします。

おまけ(テストデータ)

create table products_tbl(
        hs_id    int
    ,    ct_01    varchar(30)
    ,    ct_02    varchar(30)
    ,    ct_03    varchar(30)
    ,    ct_04    varchar(30)
    ,    ct_05    varchar(30)
    ,    ct_06    varchar(30)
    ,    name    varchar(30)
    ,    url        text
);
insert into products_tbl VALUES
 (1001,    'A','AA','AAA','A-ct4','','','name1001','http://www')
,(1002,    'A','AA','BBB','A-ct4','','','name1002','http://www')
,(1003,    'A','AA','BBB','B-ct4','','','name1003','http://www')
,(1004,    'A','AA','CCC','A-ct4','','','name1004','http://www')
,(1005,    'A','BB','BBB','A-ct4','','','name1005','http://www')
,(1006,    'A','BB','BBB','A-ct4','','','name1006','http://www')
,(1007,    'A','BB','CCC','B-ct4','','','name1007','http://www')
,(1008,    'B','AA','AAA','A-ct4','A-ct5','A-ct6','name1008','http://www')
,(1009,    'B','BB','AAA','B-ct4','B-ct5','','name1009','http://www')
,(1010,    'B','BB','BBB','A-ct4','C-ct5','','name1010','http://www')
;

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/08/07 00:57

    tomari_performさん
    ありがとうございます。
    convert.sqlを自動で作りたいのんです。
    convert.sqlには親子関係が記されています
    例えば「AA」の親は「A」なので「AA」の parent_id は「A」のid=1が入ります
    「AAA」の親は「AA」なので「AAA」のparent_id は「AA」のid=3が入ります。
    少し難しいですがよろしくお願い致します。

    キャンセル

  • 2017/08/07 01:00

    PHPに複数のSQLを実行させても構いません

    キャンセル

  • 2017/08/07 01:43

    複数のSQLを実行しても良い条件下であれば、
    難しくないですよ(私にとっては)。
    ヒントだけ別回答しておきます。

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 90.01%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる
  • トップ
  • PHPに関する質問
  • RDB(mysql)からリストメニュー。入れ子集合モデルを作る前段階の隣接リストモデル作成