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

Question

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

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文にすれば良いのでしょうか？

mpro-gram · Accepted Answer

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文考えるの面倒だけど。

mpro-gram · Answer

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

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

mpro-gram · Answer

他の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が結局楽。

yambejp · Answer

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

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

delete 文のエラーは、相関クエリかどうかよりも、 where 句内のサブクエリのfrom句に直接、deleteやinsert の対象テーブルが置けないということだった。

表示順変更は、そういうことです、ある階層だけ逆とか、２つの間に入れたいなーとかあっても、元テーブル側のidはどうしても登録順になってしまうから。

他のtree構造形式に比べると、１ノードが出現する行が必ず複数存在する（そのうえ、ツリーのどのあたりにいるのかで行数が変わる）ので、ツリー編集は１SQL文ではまかないきれないことがほとんど。

通常の木構造とだいぶ趣向が違いますが

この回答への補足

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング