質問編集履歴

2 誤字の修正

urdapple

urdapple score 68

2015/08/04 13:10  投稿

中間テーブルからの検索方法
料理テーブル(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
一応どうにか考えたクエリです
```sql
select *
from A
join
(/* 以下のクエリで材料テーブルを横に並べ替えている */
   select
       aid,
        /* ↓の2行が、検索する材料が増えると増えていく */
       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 = 2 then 1 else null end) = 1 then 1 else 0 end as material_2
       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文にできるんでしょうか???
  • SQL

    3152 questions

    SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

1 誤字の修正

urdapple

urdapple score 68

2015/08/04 13:09  投稿

中間テーブルからの検索方法
料理テーブル(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
一応どうにか考えたクエリです
```sql
select *
from A
join
(/* 以下のクエリで材料テーブルを横に並べ替えている */
   select
       aid,
        /* ↓の2行が、検索する材料が増えると増えていく */
       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 = 2 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文にできるんでしょうか???
  • SQL

    3152 questions

    SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

思考するエンジニアのためのQ&Aサイト「teratail」について詳しく知る