配方数据库，按成分搜索 [英] Recipe Database, search by ingredient

问题描述

我的数据库中有以下3个表，在查询它们以获得所需结果时遇到了一些麻烦.我正在尝试按成分搜索食谱.

I have the following 3 tables in my database, and am having some trouble querying them for the results I want. I'm trying to search for recipes by ingredients.

以下模式的SQL Fiddle: fiddle

SQL Fiddle of the schema below: fiddle

这是我的桌子: 成分

+---------------+---------+
| ingredient_id | name    |
+---------------+---------+
|             1 | tomato  |
|             2 | onion   |
|             3 | rice    |
|             4 | chicken |
|             5 | beef    |
|             6 | noodles |
|             7 | salt    |
+---------------+---------+

食谱

+-----------+------------------+
| recipe_id | name             |
+-----------+------------------+
|         1 | tomato goodness  |
|         2 | meat deluxe      |
|         3 | chicken surprise |
+-----------+------------------+

Ingredient_Index

Ingredient_Index

+-----------+---------------+
| recipe_id | ingredient_id |
+-----------+---------------+
|         1 |             1 |
|         1 |             5 |
|         1 |             7 |
|         2 |             5 |
|         2 |             6 |
|         2 |             7 |
|         3 |             4 |
|         3 |             3 |
|         3 |             7 |
+-----------+---------------+

仅搜索一种成分的查询可以正常工作，并输出以下内容:

a query to search for only one ingredient works fine, and outputs this:

mysql> select r.recipe_id, r.name
    -> from recipes r
    -> inner join ingredient_index
    -> on i.recipe_id = r.recipe_id
    -> where
    -> i.ingredient_id = 7;

+-----------+------------------+
| recipe_id | name             |
+-----------+------------------+
|         1 | tomato goodness  |
|         2 | meat deluxe      |
|         3 | chicken surprise |
+-----------+------------------+

但是当使用或用于多种成分时，我们会得到这个

But when using or for multiple ingredients we get this

mysql> select r.name
    -> from recipes r
    -> inner join ingredient_index i
    -> on i.recipe_id = r.recipe_id
    -> where i.ingredient_id = 7 or i.ingredient_id = 5;

+------------------+
| name             |
+------------------+
| tomato goodness  |
| tomato goodness  |
| meat deluxe      |
| meat deluxe      |
| chicken surprise |
+------------------+

组5行(0.00秒)

不使用"and"结果

    mysql>  select r.name
    ->  from recipes r
    ->  inner join ingredient_index i
    ->  on i.recipe_id = r.recipe_id
    ->  where i.ingredient_id = 7 and i.ingredient_id = 5;
Empty set (0.00 sec)

任何帮助将不胜感激！

推荐答案

由于配方可以使用多种成分，并且您正在寻找使用指定一种或多种成分的配方，因此应使用DISTINCT关键字来防止如果配方使用指定列表中的一种以上成分，则结果重复.另外，您可以使用IN子句来过滤多个成分ID.

Since a recipe can use multiple ingredients and you are looking for recipes that use one or more of the ingredients specified, you should use the DISTINCT keyword to prevent duplicate results where a recipe is using more than one ingredient from the list specified. Also, you can use IN clause to filter on multiple ingredient IDs.

select DISTINCT r.name
from 
    recipes r
    inner join ingredient_index i
    on i.recipe_id = r.recipe_id
where i.ingredient_id IN (7, 5);

或者，如果要查找使用列表中指定的所有成分的配方，则可以按配方名称对结果进行分组，并检查记录数是否与列表中的成分数相同. /p>

Alternatively, if you are looking for recipes that are using all the ingredients specified in the list, then you can group the results by recipe name and check if the count of records is same as the number of ingredients in your list.

select r.name
from 
    recipes r
    inner join ingredient_index i
    on i.recipe_id = r.recipe_id
where i.ingredient_id IN (7, 5)
GROUP BY r.name
HAVING COUNT(*) = 2

这是假设不会有重复的记录具有相同的(recipe_id，Ingredient_id)元组(最好通过UNIQUE约束确保).

This is assuming that there won't be duplicate records with same (recipe_id, ingredient_id) tuple (better ensured with a UNIQUE constraint).

这篇关于配方数据库，按成分搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！