このジャンルでお願いします。
次のようなテーブルで
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ベストアンサー
- 回答日時:
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 取得, ここでは2つのサブクエリがあるので、2つそれぞれ発行が必要
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文でいけるから、2文必要なのは枝替えだけかな?でも、自由編集だとこの枝替えのほうが頻繁に発生するような気がしたので、頻度認識がずれていたようです。
あと、同じ親内部での兄弟ノード表示順変更は、番号自体の変更が必要になったりするので、もとの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
- 回答日時:
表示順変更は、そういうことです、ある階層だけ逆とか、2つの間に入れたいなーとかあっても、元テーブル側のidはどうしても登録順になってしまうから。
連番とか使ってると、置き換えるにも、重なりが生じると、狂ってしまうので、文字列コードとして、ascii順で、中間があるように名前を付けておくなどしてました。経路列挙の方式だったので、あとから、順番変えるときは、そのコード名を被らないように変更して一括置換しました。
ご呈示の閉包テーブルの場合、ご指摘のように、item テーブル側にもう一つカラムをdisplay_idなどの名前で追加して、文字列か、数値で空きのある番号で振るとかとしてunique indexを貼り、
閉包テーブル側は、FOREIGN KEYの連結先をこの表示順制御用カラムに変更すれば、ON UPDATE CASCADE条項により変更が反映されるかと思ったけど、
試してみたら、閉包テーブル側2カラムに対し、item側が同一カラムを参照しているので、片方づつ反映しようとしてエラーが出ますね。
mysqlの日本語マニュアルオンラインは消えちゃったけど、mysql5.1時代のをダウンロードは出来るので、それを読むと、外部キー制約に ON UPDATE CASCADE付けるときのやってはいけないこととして記述されています。ON UPDATE no action だと結局変更できないですし。
削除に関しては問題なく自動削除されるようです。兄弟順は気にしないことにするなら、このままでもいいかとも思うけど。
ご回答ありがとうございます。
なるほど、そういう制約があったら厳しそうですね・・・
非常に参考になりました。
ありがとうございます。
No.2
- 回答日時:
他のtree構造形式に比べると、1ノードが出現する行が必ず複数存在する(そのうえ、ツリーのどのあたりにいるのかで行数が変わる)ので、ツリー編集は1SQL文ではまかないきれないことがほとんど。
で、triggerかprocedureかにしたり、プログラム側で複数行のdelete insert を行ったりが必要です。以前に、SQLパズルなのりでやろうとして、閉包テーブルではこの枝のすげ替えで果てた。
nest set や 経路列挙は、上位や下位の抽出に自己結合を頻繁に使うが、枝のすげ替えも何とかなる。でも、規則に則った編集がいるので、各種操作に対応してプログラム側で、適宜sql文を組む必要があるのは、どれを使っても同じ。
-- 閉包テーブル編集 枝のすげかえ(直上親の変更)
上位 元親 と 下位の全ノードとの対応行に変更必要 変更後行数も違うので update は不適 →
まず、元親 ascendant に繋がる下位ノード用の行削除、
すげ替えたいノードとその下位同士のツリー関係行はさわらないでもよい
変更後の親に繋がる下位ノードリストを 行挿入
itemテーブルに変更はないから、トリガーにすべきものがないので、プログラム側で組むか、procedure か。
で、上記の削除と挿入を実行という2クエリ必要、ということで、トランザクション内での実行も必要
-- SQL例
start transaction;
-- 1. 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 );
-- 2. すげ替え先 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が結局楽。
ご回答ありがとうございます。
>ツリー編集は1SQL文ではまかないきれないことがほとんど
今回の質問(あるノード以下を別のノード下に移し替える)以外に
他にツリー編集するケースとはどういうものなのでしょうか?
ご回答のSQLを試してみたのですが、
「You can't specify target table 'treepath' for update in FROM clause 」
というエラーが出てしまいます・・・
>個別にリスト取得して、プログラム側で in リストに数値を書き入れたSQL
とは具体的にはどのようなSQL文になるのでしょうか?
No.1
- 回答日時:
通常の木構造とだいぶ趣向が違いますが
このデータ構造でないといけないのでしょうか?
普通はデータを単純化するため親だけをもつか、フルパスでもつか
検索性をたかめた入れ子形式にすると思いますが・・・
ご回答ありがとうございます。
自分としてはつい最近以下のようなサイトで
http://it-phlogiston.doorblog.jp/archives/251478 …
で閉包テーブルという存在を知ったのですが、
たしかにこのテーブルを利用すれば、例えば次のようなテーブルがあったとして
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_tr …
入れ子集合モデルは、自分のやりたいこととしては
itemの追加更新削除はユーザーにやらせたいというのがあって
左から右までの範囲(数値)を最初から決められないというのがあります・・・
http://qiita.com/hirashunshun/items/06adf4f42f03 …
yambejpさんはその点についてはどうお考えでしょうか?
例えば上の例(log_item)は他のやり方でも可能なのでしょうか?
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- MySQL 次の時間帯の勝率の合計を求めるSQL文 1 2023/07/04 17:12
- PHP php エラー 2 2022/10/23 16:43
- PHP PHP MySQLに画像を直接保存 2 2022/06/05 11:50
- PHP プログラムコードを入力する場合改行してもいいですか? 2 2022/10/02 16:05
- MySQL my_itemsテーブルのIDにAUTO_INCREMENT を追加ができるかで 1 2023/01/03 09:09
- MySQL `picture` varchar(255) のコマンドで間違いないでしょうか? 1 2022/11/21 04:08
- MySQL エラー 1068 (42000): 複数の主キーが定義されていますエラー 2 2022/11/17 04:36
- MySQL テーブル作成です。どこかのスペルが間違っているか記号など スペースかな? 1 2022/10/01 05:08
- Access(アクセス) access,vbaでフォルダ内のファイルをテーブルにインポート、ファイル名もフィールドに追加したい 1 2022/08/31 11:11
- PHP ここで言う「アロー演算子」の役割を教えてください。 1 2022/03/26 02:38
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルで最後の文字だけ置き...
-
エラー 1068 (42000): 複数の主...
-
Mysql UPDATE出来ません
-
select文のwhere句に配列を入れ...
-
SQLについての質問
-
副問合せの書き方について
-
LEFT JOINが2つあるSQL文でAND...
-
mysqlで変数を使う方法
-
【初心者】特定の文字に色をつ...
-
エクセルの関数について教えて...
-
期間の重複を調べるSQL文につい...
-
ローカルルーターモードとは
-
PL/SQLの変数について
-
SQLサーバから、項目の属性(型...
-
SQLにて特定の文字を除いた検索...
-
group by 関数で表示するカラム...
-
閉包テーブルのノードの移行に...
-
Unionした最後にGROUP BYを追加...
-
sqlで、600行あるテーブルを100...
-
カンマ区切りの文字列を検索する
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルで最後の文字だけ置き...
-
SQL Left Join で重複を排除す...
-
SQLサーバから、項目の属性(型...
-
副問合せの書き方について
-
VIEWの元のテーブルのindexって...
-
エクセルの関数について教えて...
-
SQLにて特定の文字を除いた検索...
-
マイクラPC版のコマンドで効率...
-
select文のwhere句に配列を入れ...
-
sqlで、600行あるテーブルを100...
-
ある条件の最大値+1を初番する...
-
inner joinをすると数がおかし...
-
Unionした最後にGROUP BYを追加...
-
期間の重複を調べるSQL文につい...
-
クエリ表示と、ADOで抽出したレ...
-
Access パラメータクエリをcsv...
-
PL/SQLの変数について
-
MySQLのDATE型カラム値がNULLの...
-
php+mysqlで複数選択削除について
-
上位3位を求めるSQL文は?
おすすめ情報