このジャンルでお願いします。
次のようなテーブルで
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.4
- 回答日時:
補足
よくよく考えたら更新の際、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.3ベストアンサー
- 回答日時:
修正版、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.2
- 回答日時:
いろいろ試してみましたが、プロシージャで処理するのが一番楽かも
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
- 回答日時:
親情報しか持たないデータ構造だとかなり冗長な処理になりますが
大丈夫ですか?
>'allow'である場合のみ取得したいのです。
親がdenyで自分がallowや
親の親がdenyで親や自分がallowという場合どうしますか?
すみません、たしかに説明不足な条件がありました。
>親がdenyで自分がallow
自分allowなら上の階層がどうであろうと取得
自分denyなら上の階層がどうであろうと取得しない
それでいうと
>親の親がdenyで親や自分がallow
ならば取得です。
親の親の親がallow
親の親がdeny
親がallow
で自分が何もない(allowもdenyも)場合も取得です。
とにかく自分(=1番の直近)も含めて直近のallow、denyに従って取得したいのです。
>親情報しか持たないデータ構造だとかなり冗長な処理になりますが
とりあえず教えて頂きたいです。
お願いします。
うーん、自分としてはこのような構造にしたいのですが、このやり方は良くないですか?
もっと良いデータ構造なりテーブル構成があればそちらにしたいとは思いますが
この例だと、
あるユーザーの持っているアイテムを他のユーザーに
(なくならないアイテムと仮定して)使用許可の有無を持たせる構造にしたのです。
もしアドバイスがあれば頂ければ幸いなのですが、よろしくお願い致します。
お探しの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
- MySQL mysqlで INSERT と SELECTの用途は 1 2022/04/01 00:45
- PHP プログラムコードを入力する場合改行してもいいですか? 2 2022/10/02 16:05
- その他(プログラミング・Web制作) pythonのグローバル変数 2 2022/11/25 18:02
- MySQL `picture` varchar(255) のコマンドで間違いないでしょうか? 1 2022/11/21 04:08
- MySQL テーブル作成です。どこかのスペルが間違っているか記号など スペースかな? 1 2022/10/01 05:08
- MySQL エラー 1068 (42000): 複数の主キーが定義されていますエラー 2 2022/11/17 04:36
- PHP ここでの ②if($su_d<>"")の比較演算子 を使う理由は 1 2022/03/26 02:33
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルで最後の文字だけ置き...
-
SQLサーバから、項目の属性(型...
-
SQL Left Join で重複を排除す...
-
副問合せの書き方について
-
[MySQL] UNIQUE制約の値を更新...
-
select文のwhere句に配列を入れ...
-
SQLにて特定の文字を除いた検索...
-
selectした大量データをinsert...
-
エクセルの関数について教えて...
-
sqlで、600行あるテーブルを100...
-
MySQLのint型で001と表示する方...
-
VIEWの元のテーブルのindexって...
-
複数テーブルのGROUP BY の使い...
-
Unionした最後にGROUP BYを追加...
-
Access パラメータクエリをcsv...
-
LAST_INSERT_IDで同時にアクセ...
-
PL/SQLの変数について
-
[SQLServer] テーブル名からカ...
-
VMwareがCDドライブを認識する...
-
1対多結合で多を絞り込み条件と...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルで最後の文字だけ置き...
-
SQL Left Join で重複を排除す...
-
副問合せの書き方について
-
SQLサーバから、項目の属性(型...
-
VIEWの元のテーブルのindexって...
-
select文のwhere句に配列を入れ...
-
selectした大量データをinsert...
-
センノシド異性体構造式
-
Unionした最後にGROUP BYを追加...
-
insertを高速化させたい
-
SQLにて特定の文字を除いた検索...
-
マイクラPC版のコマンドで効率...
-
ある条件の最大値+1を初番する...
-
inner joinをすると数がおかし...
-
sqlで、600行あるテーブルを100...
-
エクセルの関数について教えて...
-
Access パラメータクエリをcsv...
-
URL と行番号の指定
-
複数テーブルのGROUP BY の使い...
-
PL/SQLの変数について
おすすめ情報