中間テーブルからの検索方法

Question

料理テーブル（A）と材料テーブル（B）があったとして、その料理に使う材料の関係性をつなぐ中間テーブル（C）があったとします。（いわゆるhasAndBelongsToManyな関係のテーブルです）
材料として、りんご、はちみつを検索し、料理テーブルからカレーとアップルパイを引っ張りだすSQL文が浮かびません。
材料が少なければサブクエリを繋いで探す方法はなんとなーく浮かんだんですけれど、材料が増えてくるとその分だけサブクエリも増え、著しくレスポンスが悪くなる気がします。

なるべくレスポンスを悪くせずに検索する方法はありますでしょうか？

Aのカラム（他にもカラムはある、値段等々）
idとcook（料理名）
1,カレー
2,生姜焼き
3,プリン
4,アップルパイ

Bのカラム
idとmaterial（素材名）
1,リンゴ
2,豚肉
3,ハチミツ
4,牛乳
5,砂糖
6,塩

Cのカラム
idとAid、Bid
1,1,1
2,1,3
3,1,4
4,2,2
5,2,6
6,3,3
7,3,4
8,3,5
9,4,1
10,4,3
11,4,5

一応どうにか考えたクエリです

select *
from A
join
(/* 以下のクエリで材料テーブルを横に並べ替えている */
	select
		aid,
		 /* ↓の２行が、検索する材料が増えると増えていく */
		case when sum(case when bid = 1 then 1 else null end) = 1 then 1 else 0 end as material_1,
		case when sum(case when bid = 3 then 1 else null end) = 1 then 1 else 0 end as material_2
	from C
	group by aid
	having material_1 = 1 and material_2 = 1 /* ここも材料分増える */
) as B
on A.id = B.aid

何かあんまりスマートじゃないですよね・・・
中間テーブルが増えたらかなりレスポンスも悪くなるような気もしますし・・・

ちなみにcakephpで開発中なんですけれど、上のSQLを効率よくcakephpのfind文にできるんでしょうか？？？

Answer 1

一つの料理に対して同じ素材が重複してない前提があるなら（一意性制約とかで）、次のように in で条件の素材を羅列して、having で条件の数を指定する、とかでできないでしょうか。

select A.id, A.cook from A
  inner join C on A.id = C.aid
  inner join B on B.id = C.bid
where B.id in (1, 3)
group by A.id, A.cook
having count(*) = 2

distinct で重複を除外してもいいかもしれません。

select A.id, A.cook
from A
  inner join C on A.id = C.aid
  inner join B on B.id = C.bid
where B.id in (1, 3)
group by A.id, A.cook
having count(distinct B.id) = 2;

Comments

[urdapple]

おおおおおぉおぉ！
素材重複していないです。
なので、これレスポンス良さそうですね？

select A.id, A.cook from A
inner join C on A.id = C.aid
inner join B on B.id = C.bid
where B.material in ('リンゴ', 'ハチミツ') /* ここと */
group by A.id, A.cook
having count(*) = 2 /* ここを、素材が増える毎に増やせばいいわけですね */

ちょっとやってみます。
ありがとうございましたm(_ _)m

Answer 2

回答になっていないかもしれませんが、
料理名素材名に関するデータ数があまり多くないなら、あらかじめ全レコードを読み込んで変数に保持した方が処理速度が向上する場合があります。複雑なSQL文を書く必要もなくなり、結果メンテナンス性が向上します。

Comments

[urdapple]

実際にはちょっと違う項目名なのですが、それぞれに1000件以上あり、中間テーブルの件数は万単位なんです。
ですので、できればSQLでの処理をしたいんです。

でも提案のご提示ありがとうございました。