多対多の親子関係のテーブルから子供のデータの組み合わせで検索したい。

Question

こんにちは。

以下のような多対多構造のテーブルがあったときの質問です。

【テーブル構造】
test_cases
・id
・name

test_case_test_conditions
・id
・test_cases_id
・test_condition_id

test_conditions
・id
・name

【その他の前提】
このとき、アプリケーション側の仕様として、
test_casesに対してtest_case_test_conditionsの組み合わせを一意とします。

ex.
OKパターン
test_case_test_conditions

id	test_cases_id	test_condition_id
1	1	1
2	1	2
3	2	1

NGパターン
test_case_test_conditions

id	test_cases_id	test_condition_id
1	1	1
2	1	2
3	2	1
4	2	2

【質問】
SQLで以下の条件のデータを取得するにはどのようにしたら良いでしょうか?
1.指定したtest_conditionの組み合わせがすでに存在するか?
2.指定したtest_conditionをすべて持つtest_conditonをselectする。
ex: test_conditon_id[1,2]が指定されたときに、test_condition_id[1,2][1,2,3]や[1,2,4]など指定したtest_conditon以上の条件で絞り込みたい。
3.指定したtest_conditionの一部のみを持つtest_conditonをselectする。
ex: test_conditon_id[1,2,3]が指定されたときに、test_condition_id[1,2,3][1,2]や[1][2][3]など指定したtest_conditon以下の条件で絞り込みたい。[1,4]など指定していないtest_conditon_idを持つものは除外したい。

指定したtest_conditionはアプリケーション側から動的に指定されます。
複雑なSQLとなり、困惑して思考停止気味です。。。

アイデアが思いついた方、お手数ですがアドバイスいただけると幸いです。

Comments

[yambejp]

「test_casesに対してtest_case_test_conditionsの組み合わせを一意」という命題に対して
OKパターンでも一意になっているように見えませんが
どういうロジックか明確にしてもらえませんか？

[yukikp]

test_cases_id => test_condition_idの組み合わせで、
1 => 1
1 => 2
2 => 1
がOKパターンで
1 => 1
1 => 2
2 => 1
2 => 2
がNGパターンという意味がよく分からないです。

[sazi]

OKパターンが
1 => 1
1 => 2
2 => 2
なら、組み合わせとして重複しないという意味で理解はできますけどね

[m.ts10806]

>複雑なSQLとなり
「複雑なSQLであれば対応可能」とも取れますが、実際はどうなのでしょうか。
もちろんパフォーマンスなども考慮したいというのでしたらその方向でのアドバイスがあるかもしれませんが、実際のところ現在の質問内容からは判断しかねる部分です

[GOROGORO]

コメントありがとうございます。
回答させていただきます。

** 組み合わせを一意 ** についてですが、
1 => 1
1 => 2
というレコードがあっても、
2 => 1や
3 => 2や
4 => 1
4 => 2
4 => 3
はOKとしたいが、
5 => 1
5 => 2
といった一つのtest_cases_idの中に属するtest_condition_idの組み合わせが同じな場合はNGとしたい。という意図です。
test_condition_idの組み合わせが一部不足していたり、それ以上のものが指定されていたらOKということです。
また、データ登録時にこれらのOK,NGを判定してinsertやupdateを切り替えるような処理を作りたいと考えています。

分かりづらく申し訳ございません。

[m.ts10806]

質問は編集できますので適宜、追記いただければと。

[GOROGORO]

** 複雑なSQLとなり **

はい。もちろんパフォーマンスにも考慮できたSQLとしたいです。
ただ、今現在どのような構文だったら正常に動作するかもわかっておらず。。。
ひとまずはどのようなデータ取得の方法があるのか、アドバイスいただければ、。。という状況です。

コメントありがとうございます。

[yambejp]

test_cases_idごとにtest_condition_idの組み合わせが完全に同等のものをNGとするのですね？
その場合度のタイミングでチェックしてどう弾くのでしょうか
1,1、1,2が登録されていて2,1はOKで2,2が登録されようとした瞬間はじく？
でも実は2,1、2,2、2,3かもしれないでしょ？

[GOROGORO]

コメントありがとうございます。

はい。登録時はアプリケーション側でtest_casesを登録する前に、そのtest_casesに紐づくconditionの組み合わせがDBに存在するかをチェックしてから登録処理を流したいと考えています。

Answer 1

group_concatでパターンを生成し、登録しようとするパターンがあるかどうかをチェックする。

select count(*)
from (
  select test_cases_id, group_concat(test_condition_id order by test_condition_id) pattern
  from test_case_test_conditions
  group by test_cases_id
) test
where pattern='1,2,3' -- '1,2,3は登録しようとするパターン

※count(*)>1ならパターンが存在

Answer 2

プロシージャとトランザクションを使った例

• 初期データ

create table test_case_test_conditions(id int primary key auto_increment,test_case_id int,test_condition_id int,unique(test_case_id,test_condition_id));
insert into test_case_test_conditions(test_case_id,test_condition_id) values(1,1),(1,2),(2,1),(3,1),(3,3),(4,1),(4,2),(4,4);

test_case_id	test_condition_id
1	1,2
2	2
3	1,3
4	1,2,4

• procedure作成

drop procedure if exists test;
delimiter //
create procedure test(IN flg tinyint)
begin
if flg=1 then
commit;
else
rollback;
end if;
end
//
delimiter ;

※引数が1だったときだけcommitする、それ以外はrollback

• test1

5:1,2を挿入してみる

start transaction;
set @a=0;
insert ignore into test_case_test_conditions(test_case_id,test_condition_id) values(5,1),(5,2);
select count(*) as cnt into @a from(
select test_case_id,group_concat(test_condition_id order by test_condition_id asc) as conditions
from test_case_test_conditions
group by test_case_id
) as t1 group by conditions order by cnt desc limit 1;
call test(@a);

※挿入されない（test_case_id=1と同値のため）

• test2

6:2を挿入してみる

start transaction;
set @a=0;
insert ignore into test_case_test_conditions(test_case_id,test_condition_id) values(6,2);
select count(*) as cnt into @a from(
select test_case_id,group_concat(test_condition_id order by test_condition_id asc) as conditions
from test_case_test_conditions
group by test_case_id
) as t1 group by conditions order by cnt desc limit 1;
call test(@a);

※挿入される

• test3

7:1,2,3を挿入してみる

start transaction;
set @a=0;
insert ignore into test_case_test_conditions(test_case_id,test_condition_id) values(7,1),(7,2),(7,3);
select count(*) as cnt into @a from(
select test_case_id,group_concat(test_condition_id order by test_condition_id asc) as conditions
from test_case_test_conditions
group by test_case_id
) as t1 group by conditions order by cnt desc limit 1;
call test(@a);

※挿入される

Answer 3

こんな感じで、test_condition_idの組み合わせチェックはできるかな？

SELECT
COUNT(*)
FROM
(
SELECT
GROUP_CONCAT( test_condition_id ORDER BY test_condition_id ) AS test_condition_id_group
FROM
test_case_test_conditions
GROUP BY
test_cases_id
) test_condition_set
WHERE
//'%1,2%'だ11,21も選ばれるため場合分けしてる。
//上でtest_condition_idの順にGROUP_CONCATさせてるので、1と2は入れ替えなくていいはず。
test_condition_id_group LIKE '1,2'
OR
test_condition_id_group LIKE '%,1,2'
OR
test_condition_id_group LIKE '1,2,%'
OR
test_condition_id_group LIKE '%,1,2,%'

Answer 4

皆様ありがとうございました！頂いたアドバイスを元に進みそうです！ありがとうございました！