はじめに
SQLで「複数のカラムのいずれかに特定の文字列が含まれているか」を調べる方法で興味深い記事を見つけたので少し考えてみました。
いずれかのカラムに検査値が存在するかを調べる場合、 "カラム名" = "検査値" を OR条件で繋げる 方法がありますが、
これを "検査値" in ("カラム名") で行うというものです。
（左辺がカラムで右辺が検査値という凝り固まった考えがあったので、その発想はなかった）
このやり方の良いところは、複数の検査値（たとえば「サラダ」と「お好み焼き」）が一致するデータを検索したい場合に、 "検査値" in ("カラム名")をOR条件で繋げるだけで実現できることです。
menusテーブル
| id | ingredient | food_1 | food_2 | food_3 | food_4 | food_5 |
|---|---|---|---|---|---|---|
| 1 | キャベツ | サラダ | お好み焼き | 回鍋肉 | 野菜炒め | ポトフ |
| 2 | 人参 | ポトフ | 野菜炒め | サラダ | カレー | 漬物 |
| 3 | 玉ねぎ | カレー | ポトフ | ミートソース | 野菜炒め | NULL |
| 4 | ジャガイモ | 肉じゃが | カレー | ジャーマンポテト | じゃがバター | 味噌汁 |
| 5 | ブロッコリー | 野菜炒め | アーリオオーリオ | ポトフ | NULL | NULL |
| 6 | ピーマン | サラダ | 肉詰め | 野菜炒め | ポトフ | NULL |
このテーブルに対して、「サラダ」と「お好み焼き」で共通の材料を検索する
SELECT
*
FROM
menus
WHERE
"サラダ" IN (food_1,food_2,food_3,food_4,food_5)
AND "お好み焼き" IN (food_1,food_2,food_3,food_4,food_5)
| id | ingredient | food_1 | food_2 | food_3 | food_4 | food_5 |
|---|---|---|---|---|---|---|
| 1 | キャベツ | サラダ | お好み焼き | 回鍋肉 | 野菜炒め | ポトフ |
DB設計的には？
しかしながら、この構成では食材(ingredient)で作ることができる料理(food)が増えた場合に、
- テーブルに新しいカラム(food_6)を追加する
- SQLのIN句にカラムを追加する
- レコードを更新する
という作業が必要になります。
では、次のようなデータの持ち方をした場合はどうでしょうか？
食材(ingredients)テーブル
| id | ingredient |
|---|---|
| 1 | キャベツ |
| 2 | 人参 |
| 3 | 玉ねぎ |
| 4 | ジャガイモ |
| 5 | ブロッコリー |
| 6 | ピーマン |
料理(foods)テーブル
| id | food |
|---|---|
| 1 | サラダ |
| 2 | ポトフ |
| 3 | カレー |
| 4 | 肉じゃが |
| 5 | 野菜炒め |
| 6 | お好み焼き |
| 7 | 肉詰め |
| 8 | アーリオオーリオ |
| 9 | 回鍋肉 |
| 10 | ジャーマンポテト |
| 11 | ミートソース |
| 12 | じゃがバター |
| 13 | 漬物 |
| 14 | 味噌汁 |
中間(ingredients_foods)テーブル
| ingredient_id | food_id |
|---|---|
| 1 | 1 |
| 1 | 2 |
| 1 | 5 |
| 1 | 6 |
| 1 | 9 |
| 2 | 1 |
| 2 | 2 |
| 2 | 3 |
| 2 | 5 |
| 2 | 13 |
| 3 | 2 |
| 3 | 3 |
| 3 | 5 |
| 3 | 11 |
| 4 | 3 |
| 4 | 4 |
| 4 | 10 |
| 4 | 12 |
| 4 | 14 |
| 5 | 2 |
| 5 | 5 |
| 5 | 8 |
| 6 | 1 |
| 6 | 2 |
| 6 | 4 |
| 6 | 7 |
このデータの持ち方であれば、料理(food)が追加になった場合
- 料理テーブルにレコードを追加する
- 中間テーブルにレコードを追加する
だけで済みますので、テーブル構成を変える必要はなくなります。
では、データを取得する場合はどうでしょうか？
単純に結合(left join)して取得すると次のようになります。
| id | ingredient | id | food |
|---|---|---|---|
| 1 | キャベツ | 1 | サラダ |
| 1 | キャベツ | 2 | ポトフ |
| 1 | キャベツ | 5 | 野菜炒め |
| 1 | キャベツ | 6 | お好み焼き |
| 1 | キャベツ | 9 | 回鍋肉 |
| 2 | 人参 | 1 | サラダ |
| 2 | 人参 | 2 | ポトフ |
| 2 | 人参 | 3 | カレー |
| 2 | 人参 | 5 | 野菜炒め |
| 2 | 人参 | 13 | 漬物 |
| 3 | 玉ねぎ | 2 | ポトフ |
| 3 | 玉ねぎ | 3 | カレー |
| 3 | 玉ねぎ | 5 | 野菜炒め |
| 3 | 玉ねぎ | 11 | ミートソース |
| 4 | ジャガイモ | 3 | カレー |
| 4 | ジャガイモ | 4 | 肉じゃが |
| 4 | ジャガイモ | 10 | ジャーマンポテト |
| 4 | ジャガイモ | 12 | じゃがバター |
| 4 | ジャガイモ | 14 | 味噌汁 |
| 5 | ブロッコリー | 2 | ポトフ |
| 5 | ブロッコリー | 5 | 野菜炒め |
| 5 | ブロッコリー | 8 | アーリオオーリオ |
| 6 | ピーマン | 1 | サラダ |
| 6 | ピーマン | 2 | ポトフ |
| 6 | ピーマン | 4 | 肉じゃが |
| 6 | ピーマン | 7 | 肉詰め |
…まぁそうなりますよね。
さて、では実際に「サラダ」と「お好み焼き」の共通の材料を検索してみましょう
SELECT
i.ingredient
FROM
ingredients i
WHERE
i.id IN (SELECT i.id FROM ingredients i LEFT JOIN ingredients_foods i_f ON i.id = i_f.ingredient_id LEFT JOIN foods f ON i_f.food_id = f.id WHERE f.food = 'サラダ')
AND i.id IN (SELECT i.id FROM ingredients i LEFT JOIN ingredients_foods i_f ON i.id = i_f.ingredient_id LEFT JOIN foods f ON i_f.food_id = f.id WHERE f.food = 'お好み焼き')
GROUP BY i.ingredient
もしくは、
SELECT
i.ingredient
FROM
ingredients i
WHERE
EXISTS (SELECT 1 FROM foods fx LEFT JOIN ingredients_foods i_fx ON fx.id = i_fx.food_id WHERE i.id = i_fx.ingredient_id AND fx.food = 'サラダ')
AND EXISTS (SELECT 1 FROM foods fx LEFT JOIN ingredients_foods i_fx ON fx.id = i_fx.food_id WHERE i.id = i_fx.ingredient_id AND fx.food = 'お好み焼き')
GROUP BY i.ingredient
| ingredient |
|---|
| キャベツ |
という感じでしょうか。いずれにしても処理の負担は大きそうですね（SQLも長くなってるし）。
もし 「サラダ」 OR 「お好み焼き」 の食材を調べたい場合は次のような感じです。
SELECT
i.ingredient
FROM
ingredients i
WHERE
EXISTS (SELECT 1 FROM foods fx LEFT JOIN ingredients_foods i_fx ON fx.id = i_fx.food_id WHERE i.id = i_fx.ingredient_id AND fx.food IN ('サラダ','お好み焼き'))
GROUP BY i.ingredient
まとめ（というより所感）
柔軟な設計をしなくて良い（カラム数が確定していて不変である）場合には、1つのレコードに必要なカラムをすべて含めた状態にするのが簡単なのかもしれません。
（そもそも論として、何でも入れることができるカラムが複数あって、それに対して複数のワードがいずれかに該当するかを検索する発想自体が誤りなのか）
柔軟性を持たせようとすると、どうしても別のところに負担がかかる（今回の場合はDBアクセス負荷）ように思いますね。
うーん…ケースバイケースということでしょうか。