Как правильно составить SQL-запрос?

Question

[di]

Уважаемые знатоки SQL.
База данных:

product(продукты)
-id(int)
-name(varchar 255)

recipe(рецепты)
-id
-name(varchar 255)
-description(text)
-cooking_time(int) //время приготовления

recipe_product(связь рецепт-продукт)
-id(int)
-recipe_id(int)
-product_id(int)
-product_weight(int) //масса продукта

cooking_method(способ приготовления)
-id(int)
-name(int)

recipe_cooking_method(связь рецепт-способ приготовления)
-id(int)
-recipe_id(int)
-cooking_method_id(int)

Необходимо получить рецепты по id продуктов и их массе, по времени приготовления и по способу приготовления.
Вот как я пока делаю:

а) по id 1-го продукта (нестрогий поиск)

SELECT recipe.id,recipe.name,recipe.description,recipe.cooking_time
FROM recipe JOIN recipe_product
ON(recipe_product.recipe_id=recipe.id AND recipe_product.product_id = 1)

б) по id 1-го продукта и массе 1-го продукта (нестрогий поиск)

SELECT recipe.id,recipe.name,recipe.description,recipe.cooking_time
FROM recipe JOIN recipe_product
ON(recipe_product.recipe_id=recipe.id AND recipe_product.product_id = 1 AND recipe_product.product_weight > 800)

Но - это не строгий поиск, а как найти рецепты в которых есть только этот продукт, а других быть не должно. А как сделать такой поиск по нескольким продуктам и рецептам? Может БД неправильно спроектировал?
Подскажите плиз. Заранее благодарю)

Answer 1

[Rsa97]

Если в таблице `recipe_product` пара (`recipe_id`, `product_id`) уникальная, то из данной таблицы можно удалить искусственный ключ `id` и сделать эту пару первичным ключом.
Поиск рецептов, в которые входят все указанные продукты:

SELECT `recipe_id`, COUNT(*) AS `count`
  FROM `recipe_product`
  WHERE (`product_id` = 1 AND `product_weight` > 100)
    OR (`product_id` = 2 AND `product_weight` BETWEEN 200 AND 400)
    OR (`product_id` = 3 AND `product_weight` < 10)
  GROUP BY `recipe_id`
  HAVING `count` = 3

Поиск рецептов, в которые входят все указанные продукты и ничего кроме них:

SELECT `recipe_id`, COUNT(*) AS `count`
  FROM `recipe_product`
  WHERE `recipe_id` NOT IN (SELECT `recipe_id` FROM `recipe_product` WHERE `product_id` NOT IN (1, 2, 3))
    AND ((`product_id` = 1 AND `product_weight` > 100)
      OR (`product_id` = 2 AND `product_weight` BETWEEN 200 AND 400)
      OR (`product_id` = 3 AND `product_weight` < 10))
  GROUP BY `recipe_id`
  HAVING `count` = 3

Comments

[Boris Köln]

Если в таблице `recipe_product` пара (`recipe_id`, `product_id`) уникальная, то из данной таблицы можно удалить искусственный ключ `id`

С технической точки зрения - да.
Но с точки зрения кулинара так делать нельзя. Потому что некоторые продукты используются в рецепте несколько раз. Например, "200 граммов сахара взбейте с яйцами.... 50 граммов сахара посыпьте на тесто сверху". В вашем же рецепте придется делать костыль "250 граммов сахара, из которых ..."

[Boris Köln]

WHERE (`product_id` = 1 AND `product_id` > 100)

Это всегда false.
Может,

WHERE (`product_id` = 1 AND `product_weight` > 100)

?

Хотя это все равно не отвечает на вопрос автора "а как найти рецепты в которых есть только этот продукт, а других быть не должно?"

[Rsa97]

Boris Korobkov, Так я специально указал условие уникальности пары.
К тому же, для такого типа рецептов нужна и другая структура, иначе вы всё равно не определите, сначала 200 грамм, потом 50, или наоборот. Необходима ещё одна сущность - шаг рецепта, к которому должны быть привязаны продукты и метод приготовления.

[Rsa97]

Boris Korobkov, Да, конечно же `product_weight`, поправил.

[Boris Köln]

а как найти рецепты в которых есть только этот продукт, а других быть не должно?

Добавить новое поле recipe.n_product, обновлять его триггером при изменении recipe_product.
Тогда в вышеуказанный запрос можно будет добавить ...FROM recipe... WHERE recipe.n_product = 3 ...

[di]

Rsa97, Boris Korobkov, Спасибо за ваши ответы. Весьма благодарен. Я недавно в одном месте использовал запросы вида

SELECT `recipe_id`, COUNT(*) AS `count`
  FROM `recipe_product`
  WHERE (`product_id` = 1 AND `product_weight` > 100)
    OR (`product_id` = 2 AND `product_weight` BETWEEN 200 AND 400)
    OR (`product_id` = 3 AND `product_weight` < 10)
  GROUP BY `recipe_id`
  HAVING `count` = 3

Знание таких команд как HAVING это круто, но такие запросы очень сложно формировать динамически и у меня сортировка ломалась(я еще там ORDER BY запихивал в запрос )))

Если в таблице `recipe_product` пара (`recipe_id`, `product_id`) уникальная, то из данной таблицы можно удалить искусственный ключ `id` и сделать эту пару первичным ключом.

Ну это что-то вроде БД для умного холодильника, ну тоесть найти подходящие рецепты исходя из продуктов и их веса, так что хороший вариант.
Но кстати яйца в штуках например я вот подумал, и это печально...никто ж их не взвешивает)))

Спасибо вам еще раз))) Очень помогли)))

[Rsa97]

Delgus,

Но кстати яйца в штуках например я вот подумал, и это печально...никто ж их не взвешивает)))

Для этого обычно продукту добавляют поле `units` - единицы измерения - штуки, граммы, ложки, стаканы и т.д.

Answer 2

[Boris Köln]

Необходимо получить рецепты по id продуктов и их массе, по времени приготовления и по способу приготовления.

join всех необходимых таблиц с условиями

как найти рецепты в которых есть только этот продукт, а других быть не должно? А как сделать такой поиск по нескольким продуктам и рецептам?

INNER JOIN recipe_product AS recipe_product1
ON ... 
AND recipe_product1.product_id = $productId1

INNER JOIN recipe_product AS recipe_product2
ON ... 
AND recipe_product2.product_id  = $productId2

LEFT JOIN recipe_product AS recipe_product_other
ON ... 
AND recipe_product_other.product_id NOT IN ($productId1, $productId2)
...
WHERE recipe_product_other.id IS NULL

P.S. Эффективность такого запроса оставляет желать лучшего. Запрос с group by из соседнего ответа гораздо эффективнее.

Comments

[di]

Ну эффективность страдает зато его вроде проще динамически построить, чем вариант который мне выше предложили) Мне он нравится больше однозначно. Спасибо большое за ответ)