質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.49%

  • SQL

    2463questions

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

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

解決済

回答 2

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 2,379

urdapple

score 57

料理テーブル(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,
         /* ↓の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 = 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文にできるんでしょうか???
  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 2

checkベストアンサー

+2

一つの料理に対して同じ素材が重複してない前提があるなら(一意性制約とかで)、次のように 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;


投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2015/08/04 13:52

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

    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

    キャンセル

0

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2015/08/04 13:20

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

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

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 90.49%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る

  • SQL

    2463questions

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