階層構造のテーブルの行の取得について

質問

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

CREATE TABLE IF NOT EXISTS user (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(32) NOT NULL,
PRIMARY KEY(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO user (name) VALUES ('user1');
INSERT INTO user (name) VALUES ('user2');
INSERT INTO user (name) VALUES ('user3');

CREATE TABLE IF NOT EXISTS item (
id int(11) NOT NULL AUTO_INCREMENT,
parent_id INT,
name varchar(32) NOT NULL,
FOREIGN KEY (parent_id) REFERENCES item(id) ON DELETE CASCADE ON UPDATE CASCADE,
PRIMARY KEY(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO item (parent_id, name) VALUES (null, 'item1');
INSERT INTO item (parent_id, name) VALUES (1, 'item2');
INSERT INTO item (parent_id, name) VALUES (2, 'item3');
INSERT INTO item (parent_id, name) VALUES (3, 'item4');
INSERT INTO item (parent_id, name) VALUES (2, 'item5');
INSERT INTO item (parent_id, name) VALUES (null, 'item6');
INSERT INTO item (parent_id, name) VALUES (6, 'item7');
INSERT INTO item (parent_id, name) VALUES (null, 'item8');

CREATE TABLE IF NOT EXISTS user_item (
id int(11) NOT NULL AUTO_INCREMENT,
user_id INT,
item_id INT,
type varchar(16) NOT NULL,
PRIMARY KEY(id),
FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (item_id) REFERENCES item(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO user_item (user_id, item_id, type) VALUES (1, 1, 'allow');
INSERT INTO user_item (user_id, item_id, type) VALUES (1, 3, 'deny');
INSERT INTO user_item (user_id, item_id, type) VALUES (1, 7, 'allow');

user_itemのuser_idが1のitemの行を取得したいのですが、
その条件として

・user_itemテーブルのtypeが'allow'のitem_id以下のitemの行
・ただしtypeが'deny'のitem_id以下の行は除く

この例だと、user_itemの
INSERT INTO user_item (user_id, item_id, type) VALUES (1, 1, 'allow');
の行によってitemテーブルの
INSERT INTO item (parent_id, name) VALUES (null, 'item1');
INSERT INTO item (parent_id, name) VALUES (1, 'item2');
INSERT INTO item (parent_id, name) VALUES (2, 'item3');
INSERT INTO item (parent_id, name) VALUES (3, 'item4');
INSERT INTO item (parent_id, name) VALUES (2, 'item5');
が取得候補になりますが、
INSERT INTO user_item (user_id, item_id, type) VALUES (1, 3, 'deny');
によって
INSERT INTO item (parent_id, name) VALUES (2, 'item3');
INSERT INTO item (parent_id, name) VALUES (3, 'item4');
の行は除かれ、
INSERT INTO item (parent_id, name) VALUES (null, 'item1');
INSERT INTO item (parent_id, name) VALUES (1, 'item2');
INSERT INTO item (parent_id, name) VALUES (2, 'item5');
の行が取得されます。
つまりitemテーブルにおいて上の階層の直近の（user_itemと結合して取得した）typeが
'allow'である場合のみ取得したいのです。

INSERT INTO item (parent_id, name) VALUES (null, 'item1');←取得
INSERT INTO item (parent_id, name) VALUES (1, 'item2');←取得
INSERT INTO item (parent_id, name) VALUES (2, 'item3');
INSERT INTO item (parent_id, name) VALUES (3, 'item4');
INSERT INTO item (parent_id, name) VALUES (2, 'item5');←取得
INSERT INTO item (parent_id, name) VALUES (null, 'item6');
INSERT INTO item (parent_id, name) VALUES (6, 'item7');←取得
INSERT INTO item (parent_id, name) VALUES (null, 'item8');

複雑ではありますが、これはどのようなSQL文にすれば良いのでしょうか？

回答No.3

yambejp · ベストアンサー

修正版、itemテーブルはlevelだけ拡張しました
ユーザーごとのtypeは別テーブルで管理します。

CREATE TABLE IF NOT EXISTS item (
id int(11) NOT NULL AUTO_INCREMENT,
parent_id INT,
name varchar(32) NOT NULL,
level int NOT NULL,
PRIMARY KEY(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

→itemにデータ投入

INSERT INTO item (parent_id, name) VALUES (null, 'item1');
INSERT INTO item (parent_id, name) VALUES (1, 'item2');
INSERT INTO item (parent_id, name) VALUES (2, 'item3');
INSERT INTO item (parent_id, name) VALUES (3, 'item4');
INSERT INTO item (parent_id, name) VALUES (2, 'item5');
INSERT INTO item (parent_id, name) VALUES (null, 'item6');
INSERT INTO item (parent_id, name) VALUES (6, 'item7');
INSERT INTO item (parent_id, name) VALUES (null, 'item8');

→itemテーブルのlevelをセットします

DROP PROCEDURE IF EXISTS UPDATE_LEVEL;
DELIMITER //
CREATE PROCEDURE UPDATE_LEVEL()
BEGIN
DECLARE CNT INT;
DECLARE LVL INT;
SET LVL=1;
UPDATE item SET level=0;
UPDATE item
SET level=LVL
WHERE parent_id IS NULL;
SELECT COUNT(*) INTO CNT FROM item WHERE level=LVL;
WHILE CNT>0 DO
UPDATE item
INNER JOIN (SELECT id FROM item WHERE level=LVL) as temp ON parent_id=temp.id
SET item.level=LVL+1;
SET LVL=LVL+1;
SELECT COUNT(*) INTO CNT FROM item WHERE level=LVL;
END WHILE;
END
//
DELIMITER ;

CALL UPDATE_LEVEL();

→user_itemテーブルと、user_item_typeテーブルをつくり

CREATE TABLE IF NOT EXISTS user_item (
user_id INT,
item_id INT,
type varchar(16) NOT NULL,
UNIQUE(user_id,item_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS user_item_type (
user_id INT,
item_id INT,
type varchar(16) NULL,
UNIQUE(user_id,item_id)
);

→プロシージャを作っておきます。

DROP PROCEDURE IF EXISTS UPDATE_ITEM_TYPE;
DELIMITER //
CREATE PROCEDURE UPDATE_ITEM_TYPE(IN num INT)
BEGIN
DECLARE LVL INT;
DECLARE MAX_LVL INT;
SET LVL=2;
SELECT MAX(level) INTO MAX_LVL FROM item;
DELETE FROM user_item_type WHERE user_id=num;
INSERT INTO user_item_type(user_id,item_id,type)
SELECT num,id,type
FROM item
LEFT JOIN user_item ON user_item.item_id=item.id AND user_item.user_id=num
WHERE item.level=1;
WHILE LVL<=MAX_LVL DO
INSERT INTO user_item_type(user_id,item_id,type)
SELECT num,t1.id,COALESCE(type,
(SELECT type FROM user_item_type WHERE user_id=num AND item_id=t1.parent_id))
FROM item as t1
LEFT JOIN user_item ON user_item.item_id=t1.id AND user_item.user_id=num
INNER JOIN item as t2 ON t2.id=t1.parent_id
WHERE t1.level=LVL;
SET LVL=LVL+1;
END WHILE;
END
//
DELIMITER ;

→item_userテーブルが変更されるたびにUPDATE_ITEM_TYPEを実行します

DROP TRIGGER IF EXISTS TRG_INSERT_USER_ITEM;
DELIMITER //
CREATE TRIGGER TRG_INSERT_USER_ITEM AFTER INSERT ON user_item
FOR EACH ROW BEGIN
CALL UPDATE_ITEM_TYPE(NEW.user_id);
END;
//
DELIMITER ;

DROP TRIGGER IF EXISTS TRG_DELETE_USER_ITEM;
DELIMITER //
CREATE TRIGGER TRG_DELETE_USER_ITEM AFTER DELETE ON user_item
FOR EACH ROW BEGIN
CALL UPDATE_ITEM_TYPE(OLD.user_id);
END;
//
DELIMITER ;

DROP TRIGGER IF EXISTS TRG_UPDATE_USER_ITEM;
DELIMITER //
CREATE TRIGGER TRG_UPDATE_USER_ITEM AFTER UPDATE ON user_item
FOR EACH ROW BEGIN
CALL UPDATE_ITEM_TYPE(NEW.user_id);
END;
//
DELIMITER ;

→user_itemにデータ投入
INSERT INTO user_item (user_id, item_id, type) VALUES (1, 1, 'allow');
INSERT INTO user_item (user_id, item_id, type) VALUES (1, 3, 'deny');
INSERT INTO user_item (user_id, item_id, type) VALUES (1, 7, 'allow');
INSERT INTO user_item (user_id, item_id, type) VALUES (2, 1, 'allow');
INSERT INTO user_item (user_id, item_id, type) VALUES (2, 2, 'deny');
INSERT INTO user_item (user_id, item_id, type) VALUES (2, 8, 'allow');
INSERT INTO user_item (user_id, item_id, type) VALUES (3, 1, 'deny');

→ユーザーごとのallowを表示
SELECT id as item_id,user_id,parent_id,name,type FROM item
INNER JOIN user_item_type ON id=item_id
WHERE AND type='allow';

回答No.4

yambejp

補足
よくよく考えたら更新の際、user_idが変更になる場合は
OLD.user_idに対してもアップデートしないといけないですね

DROP TRIGGER IF EXISTS TRG_UPDATE_USER_ITEM;
DELIMITER //
CREATE TRIGGER TRG_UPDATE_USER_ITEM AFTER UPDATE ON user_item
FOR EACH ROW BEGIN
CALL UPDATE_ITEM_TYPE(NEW.user_id);
IF OLD.user_id != NEW.user_id THEN
CALL UPDATE_ITEM_TYPE(OLD.user_id);
END IF;
END;
//javascript:void(0);
DELIMITER ;

お礼

ご回答有難うございます。
なるほど、このやり方でもできました。
ありがとうございます。
たしかに一時的にテーブルを作った方が検索がシンプルになりますね。
参考になりました。

回答No.2

yambejp

いろいろ試してみましたが、プロシージャで処理するのが一番楽かも
itemテーブルを拡張してよいですか？
（itemテーブルを拡張しない場合は別テーブルをつくってjoinするので
ひと手間ふえます）
それとuserテーブルが中途半端なので、userテーブルを介さずに
itemテーブルのidとparent_idのみで結合していますのでご注意ください

CREATE TABLE IF NOT EXISTS item (
id int(11) NOT NULL AUTO_INCREMENT,
parent_id INT,
name varchar(32) NOT NULL,
level int NOT NULL,
type varchar(10) NULL,
FOREIGN KEY (parent_id) REFERENCES item(id) ON DELETE CASCADE ON UPDATE CASCADE,
PRIMARY KEY(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO item (parent_id, name) VALUES (null, 'item1');
INSERT INTO item (parent_id, name) VALUES (1, 'item2');
INSERT INTO item (parent_id, name) VALUES (2, 'item3');
INSERT INTO item (parent_id, name) VALUES (3, 'item4');
INSERT INTO item (parent_id, name) VALUES (2, 'item5');
INSERT INTO item (parent_id, name) VALUES (null, 'item6');
INSERT INTO item (parent_id, name) VALUES (6, 'item7');
INSERT INTO item (parent_id, name) VALUES (null, 'item8');

→level=自分が何階層めか
→type=自分のtype

プロシージャをつくります。
DROP PROCEDURE IF EXISTS UPDATE_ITEM;
DELIMITER //
CREATE PROCEDURE UPDATE_ITEM()
BEGIN
DECLARE CNT INT;
DECLARE LVL INT;
SET LVL=1;
UPDATE item SET level=0,type=NULL;
UPDATE item
LEFT JOIN user_item ON user_item.item_id=item.id
SET level=LVL,item.type=user_item.type
WHERE parent_id IS NULL;
SELECT COUNT(*) INTO CNT FROM item WHERE level=LVL;
WHILE CNT>0 DO
UPDATE item
INNER JOIN (SELECT id,type FROM item WHERE level=LVL) as temp ON parent_id=temp.id
LEFT JOIN user_item ON user_item.item_id=item.id
SET item.level=LVL+1,item.type=COALESCE(user_item.type,temp.type);
SET LVL=LVL+1;
SELECT COUNT(*) INTO CNT FROM item WHERE level=LVL;
END WHILE;
END
//
DELIMITER ;

→UPDATE_ITEMをコールします
CALL UPDATE_ITEM();

→itemテーブルを確認してください
SELECT * FROM item;

→type=allowのみ表示
SELECT * FROM item WHERE type='allow';

お礼

ご回答有難うございます。
なるほど、たしかに'allow'の箇所だけ取り出せました。
ありがとうございます。

＞itemテーブルを拡張してよいですか？
itemテーブルにtypeフィールドを設けてしまうと、
1ユーザ（この場合はuserテーブルのidが1）の
item情報に限定されてしまうと思うのですが、どうなんでしょうか？
このやり方でもユーザ毎の取得はできるのでしょうか？
例えば、
user_itemテーブルが

INSERT INTO user_item (user_id, item_id, type) VALUES (1, 1, 'allow');
INSERT INTO user_item (user_id, item_id, type) VALUES (1, 3, 'deny');
INSERT INTO user_item (user_id, item_id, type) VALUES (1, 7, 'allow');
INSERT INTO user_item (user_id, item_id, type) VALUES (2, 1, 'allow');
INSERT INTO user_item (user_id, item_id, type) VALUES (2, 8, 'allow');

このような場合でも意図通り取得できるのでしょうか？
そういう意味でitemテーブルは書き変えず独立させた方が良い気がするのですが、
そのようなやり方だとSQL文がより複雑になるのでしょうか？

回答No.1

yambejp

親情報しか持たないデータ構造だとかなり冗長な処理になりますが
大丈夫ですか？

＞'allow'である場合のみ取得したいのです。

親がdenyで自分がallowや
親の親がdenyで親や自分がallowという場合どうしますか？

お礼

すみません、たしかに説明不足な条件がありました。

＞親がdenyで自分がallow
自分allowなら上の階層がどうであろうと取得
自分denyなら上の階層がどうであろうと取得しない
それでいうと
＞親の親がdenyで親や自分がallow
ならば取得です。
親の親の親がallow
親の親がdeny
親がallow
で自分が何もない（allowもdenyも）場合も取得です。
とにかく自分（＝1番の直近）も含めて直近のallow、denyに従って取得したいのです。

＞親情報しか持たないデータ構造だとかなり冗長な処理になりますが
とりあえず教えて頂きたいです。
お願いします。
うーん、自分としてはこのような構造にしたいのですが、このやり方は良くないですか？
もっと良いデータ構造なりテーブル構成があればそちらにしたいとは思いますが
この例だと、
あるユーザーの持っているアイテムを他のユーザーに
（なくならないアイテムと仮定して）使用許可の有無を持たせる構造にしたのです。
もしアドバイスがあれば頂ければ幸いなのですが、よろしくお願い致します。