閉包テーブルのノードの移行について

質問

このジャンルでお願いします。
次のようなテーブルで

CREATE TABLE IF NOT EXISTS item (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(32) NOT NULL,
PRIMARY KEY(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO item (name) VALUES ('item1');
INSERT INTO item (name) VALUES ('item2');
INSERT INTO item (name) VALUES ('item3');
INSERT INTO item (name) VALUES ('item4');
INSERT INTO item (name) VALUES ('item5');
INSERT INTO item (name) VALUES ('item6');

CREATE TABLE IF NOT EXISTS treepath (
ancestor_id int(11) NOT NULL,
descendant_id int(11) NOT NULL,
FOREIGN KEY (ancestor_id) REFERENCES item(id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (descendant_id) REFERENCES item(id) ON DELETE CASCADE ON UPDATE CASCADE,
PRIMARY KEY(ancestor_id, descendant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO treepath (ancestor_id, descendant_id) VALUES (1, 1);
INSERT INTO treepath (ancestor_id, descendant_id) VALUES (1, 2);
INSERT INTO treepath (ancestor_id, descendant_id) VALUES (1, 3);
INSERT INTO treepath (ancestor_id, descendant_id) VALUES (1, 4);
INSERT INTO treepath (ancestor_id, descendant_id) VALUES (1, 5);
INSERT INTO treepath (ancestor_id, descendant_id) VALUES (1, 6);
INSERT INTO treepath (ancestor_id, descendant_id) VALUES (2, 2);
INSERT INTO treepath (ancestor_id, descendant_id) VALUES (2, 4);
INSERT INTO treepath (ancestor_id, descendant_id) VALUES (2, 5);
INSERT INTO treepath (ancestor_id, descendant_id) VALUES (2, 6);
INSERT INTO treepath (ancestor_id, descendant_id) VALUES (3, 3);
INSERT INTO treepath (ancestor_id, descendant_id) VALUES (4, 4);
INSERT INTO treepath (ancestor_id, descendant_id) VALUES (4, 6);
INSERT INTO treepath (ancestor_id, descendant_id) VALUES (5, 5);
INSERT INTO treepath (ancestor_id, descendant_id) VALUES (6, 6);

item1┬item2
　　　│　├item4
　　　│　│　　└item6
　　　│　└item5
　　　│
　　　└item3

を

item1┬item2
　　　│　│　　
　　　│　└item5
　　　│
　　　└item3
　　　　　└item4
　　　　　　　└item6

のようにitem4以下のノードをitem3の下に移したいのですが、
その場合のtreepathテーブルの更新はどのようなSQL文にすれば良いのでしょうか？

回答No.3

mpro-gram · ベストアンサー

delete 文のエラーは、相関クエリかどうかよりも、 where 句内のサブクエリのfrom句に直接、deleteやinsert の対象テーブルが置けないということだった。サブクエリ内from句にさらにサブクエリテーブルにしてやればokのようなので、row コンストラクタを使って組み合わせチェック方式にする。
-- 削除用修正
delete from treepath
where row( ascendant_id, descendant_id ) in
( select p.ascendant_id, c.descendant_id from
( select ascendant_id from treepath where descendant_id = 4 and ascendant_id !=4 ) as p ,
( select descendant_id from treepath where ascendant_id = 4 ) as c
);

「プログラム側で in リストに数値を書き入れたSQL文作成」とは、結構ごり押しで、目的SQL文を作る方法です。
-- 基本はトランザクション内での実行で、まずサブクエリにしているselect文に for update をいれて、必要id 取得, ここでは２つのサブクエリがあるので、２つそれぞれ発行が必要
select ascendant_id from treepath where descendant_id = 4 and ascendant_id != 4 for update ;
select descendant_id from treepath where ascendant_id = 4 for update ;

-- プログラムは php などで、データベース接続ですよね？取り出したidリストの配列からphpの文字列編集で以下のような文字列にしてクエリ発行
delete from treepath where ascendant_id in (1, 2) and decendant_id in (4, 6);

「ツリー編集のケース」になにがあるかは、いわれてみればそうですね。　挿入のみ、削除のみなら、上記と前回提示のinsert 文などそれぞれ1文でいけるから、２文必要なのは枝替えだけかな？でも、自由編集だとこの枝替えのほうが頻繁に発生するような気がしたので、頻度認識がずれていたようです。
あと、同じ親内部での兄弟ノード表示順変更は、番号自体の変更が必要になったりするので、もとのitem テーブルのid そのままではなく、表示順用 id みたいなのを作ったりしてました（経路列挙で作った時）。ネスト集合だと、左右位置情報で、順番替えが行われるから、表示順がidに依存しないという利点があります、SQL文考えるの面倒だけど。

お礼

ご回答有難うございます。
そのやり方でできました。
ありがとうございます。

＞あと、同じ親内部での兄弟ノード表示順変更は、番号自体の変更が必要になったりするので、もとのitem テーブルのid そのままではなく、表示順用 id みたいなのを作ったりしてました

つまりこれってこういうことですよね？

tem1┬item2
　　　│　├item4
　　　│　│　　└item6
　　　│　└item5
　　　│
　　　└item3

↓

tem1┬item3
　　　│
　　　│
　　　│
　　　│
　　　└item2
　　　　　　├item4
　　　　　　│　└item6
　　　　　　└item5

表示順用idとはitemテーブルのカラムに主キー（id）とは別に例えばdisplay_idみたいなのを設けるということでしょうか？
例えばこの閉包テーブルでもそういうことはできるのでしょうか？

回答No.4

mpro-gram

表示順変更は、そういうことです、ある階層だけ逆とか、２つの間に入れたいなーとかあっても、元テーブル側のidはどうしても登録順になってしまうから。
連番とか使ってると、置き換えるにも、重なりが生じると、狂ってしまうので、文字列コードとして、ascii順で、中間があるように名前を付けておくなどしてました。経路列挙の方式だったので、あとから、順番変えるときは、そのコード名を被らないように変更して一括置換しました。

ご呈示の閉包テーブルの場合、ご指摘のように、item テーブル側にもう一つカラムをdisplay_idなどの名前で追加して、文字列か、数値で空きのある番号で振るとかとしてunique indexを貼り、
閉包テーブル側は、FOREIGN KEYの連結先をこの表示順制御用カラムに変更すれば、ON UPDATE CASCADE条項により変更が反映されるかと思ったけど、
試してみたら、閉包テーブル側２カラムに対し、item側が同一カラムを参照しているので、片方づつ反映しようとしてエラーが出ますね。
mysqlの日本語マニュアルオンラインは消えちゃったけど、mysql5.1時代のをダウンロードは出来るので、それを読むと、外部キー制約に ON UPDATE CASCADE付けるときのやってはいけないこととして記述されています。ON UPDATE no action だと結局変更できないですし。
削除に関しては問題なく自動削除されるようです。兄弟順は気にしないことにするなら、このままでもいいかとも思うけど。

お礼

ご回答ありがとうございます。
なるほど、そういう制約があったら厳しそうですね・・・
非常に参考になりました。
ありがとうございます。

回答No.2

mpro-gram

他のtree構造形式に比べると、１ノードが出現する行が必ず複数存在する（そのうえ、ツリーのどのあたりにいるのかで行数が変わる）ので、ツリー編集は１SQL文ではまかないきれないことがほとんど。で、triggerかprocedureかにしたり、プログラム側で複数行のdelete insert を行ったりが必要です。
以前に、SQLパズルなのりでやろうとして、閉包テーブルではこの枝のすげ替えで果てた。
nest set や 経路列挙は、上位や下位の抽出に自己結合を頻繁に使うが、枝のすげ替えも何とかなる。でも、規則に則った編集がいるので、各種操作に対応してプログラム側で、適宜sql文を組む必要があるのは、どれを使っても同じ。

-- 閉包テーブル編集　枝のすげかえ(直上親の変更)
上位 元親　と 下位の全ノードとの対応行に変更必要　変更後行数も違うので update は不適　→　
まず、元親 ascendant に繋がる下位ノード用の行削除、
すげ替えたいノードとその下位同士のツリー関係行はさわらないでもよい
変更後の親に繋がる下位ノードリストを　行挿入

itemテーブルに変更はないから、トリガーにすべきものがないので、プログラム側で組むか、procedure か。
で、上記の削除と挿入を実行という２クエリ必要、ということで、トランザクション内での実行も必要
-- SQL例
start transaction;
-- １． id = 4 の上位ノード群 に連なる、 id=4 を含む下位ノード群の行を削除
-- ※ 相関クエリではないならサブクエリ内に対象テーブル使えたかな？実行チェックはしていない。サブクエリで引っかかるようなら、個別にリスト取得して、プログラム側で in リストに数値を書き入れたSQL文作成の必要もあるだろう。
delete from treepath where
ascendant_id in (select ascendant_id from treepath where descendant_id = 4 and ascendant_id != 4)
and
decendant_id in (select descendant_id from treepath where acendant_id = 4 );
-- ２． すげ替え先 id= 3 とその上位ノード群に対し、id=4 の下位ノード群を対応させる
insert into treepath (ascendant_id, descendant_id)
select p.ascendant_id, c.descendant_id
from (select ascendant_id from treepath where descendant_id = 3 ) as p
, (select descendant_id from treepath where acendant_id = 4 ) as c;
commit;
-- ※直上以外は消さなくてもと思うけど、元親と挿入先とでトップからのレベルが違っていたりすると、それをチェックするのは面倒なので、ばさっと消して、どんとinsertが結局楽。

お礼

ご回答ありがとうございます。

＞ツリー編集は１SQL文ではまかないきれないことがほとんど
今回の質問（あるノード以下を別のノード下に移し替える）以外に
他にツリー編集するケースとはどういうものなのでしょうか？

ご回答のSQLを試してみたのですが、

「You can't specify target table 'treepath' for update in FROM clause 」

というエラーが出てしまいます・・・
＞個別にリスト取得して、プログラム側で in リストに数値を書き入れたSQL
とは具体的にはどのようなSQL文になるのでしょうか？

回答No.1

yambejp

通常の木構造とだいぶ趣向が違いますが
このデータ構造でないといけないのでしょうか？
普通はデータを単純化するため親だけをもつか、フルパスでもつか
検索性をたかめた入れ子形式にすると思いますが・・・

お礼

ご回答ありがとうございます。
自分としてはつい最近以下のようなサイトで

http://it-phlogiston.doorblog.jp/archives/25147867.html

で閉包テーブルという存在を知ったのですが、
たしかにこのテーブルを利用すれば、例えば次のようなテーブルがあったとして

CREATE TABLE IF NOT EXISTS log_item (
id int(11) NOT NULL AUTO_INCREMENT,
item_id int(11) NOT NULL,
num int(11) NOT NULL,
FOREIGN KEY (item_id) REFERENCES item(id) ON DELETE CASCADE ON UPDATE CASCADE,
PRIMARY KEY(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO log_item (item_id, num) VALUES (3, 100);
INSERT INTO log_item (item_id, num) VALUES (4, 120);
INSERT INTO log_item (item_id, num) VALUES (5, 70);

item2より下の階層のnumの合計を集計したい時も
比較的簡単なSQLでできそうな気がするので閉包テーブルを利用するやり方は
良さそうな気がしました。
ただitemが増えると爆発的にレコード数が増えてしまう欠点や、
今回のような質問が分からないという問題はありますが・・・
フルパスはこのサイトを見てみたのですが、ちょっと自分の能力では
大変そうだなぁ、という印象です・・・

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

入れ子集合モデルは、自分のやりたいこととしては
itemの追加更新削除はユーザーにやらせたいというのがあって
左から右までの範囲（数値）を最初から決められないというのがあります・・・

http://qiita.com/hirashunshun/items/06adf4f42f03a9f3b63d

yambejpさんはその点についてはどうお考えでしょうか？
例えば上の例（log_item）は他のやり方でも可能なのでしょうか？

補足

閉包テーブルについて

http://labs.borders.jp/wordpress/?p=1408

も参照しています