Как отсортировать результат по количеству «пойманных» id?

Question

[hesy]

Не знаю как корректно еще назвать вопрос)

Есть таблица recipe_ingredients:

+----+-----------+---------------+
| id | recipe_id | ingredient_id |
+----+-----------+---------------+
| 1  | 100       | 500           |
+----+-----------+---------------+
| 2  | 100       | 501           |
+----+-----------+---------------+
| 3  | 200       | 501           |
+----+-----------+---------------+
| 4  | 200       | 900           |
+----+-----------+---------------+
| 5  | 200       | 901           |
+----+-----------+---------------+

Как при выборке отсортировать результаты по количеству наличия ingredient_id с одним recipe_id (назвать поле, например, catch_ingredients)?

SELECT 
  recipe_id, 
  catch_ingredients 
FROM 
  recipe_ingredients
WHERE 
  ingredient_id IN (501, 900) 
GROUP BY 
  recipe_id 
ORDER BY 
  catch_ingredients DESC

Чтобы получить такой результат:

+-----------+-------------------+
| recipe_id | catch_ingredients |
+-----------+-------------------+
| 200       | 2                 | <--- Поймали 501 и 900 
+-----------+-------------------+
| 100       | 1                 | <--- Поймали только 501
+-----------+-------------------+

Моих знаний SQL не хватает для реализации этого..
Скорее всего, здесь требуется не оператор IN.

Объясню простым языком, что я хотел сделать:
Есть условно холодильник с множеством ingredient_id.
Нужно достать из него все recipe_id, где встречаются ingredient_id (составить список релевантных рецептов из доступных продуктов), НО, рецепту не обязательно содержать все передаваемые ингредиенты (например, передали лук, картошку, курицу, сахар, апельсин, показали рецепты с "картошка с курицей" и "сок из апельсина")
В случае с ingredient_id IN ... он выбирает где строго должны быть все перечисленные айди.

recipe_id, ingredient_id имеют отношения с другими таблицами

ps, пилю пет-проект ради интереса, но на главной фиче застопорился и туплю)

Answer 1

[Дмитрий Гординский]

SELECT value, COUNT(*)
FROM table
GROUP BY 1
ORDER BY 2 DESC

Comments

[hesy]

Благодарю за ответ, но это не совсем то, что описано в вопросе :(

[Дмитрий Гординский]

hesy, тогда читайте про LEFT JOIN

[hesy]

Дмитрий Гординский,

Получилось это, не знаю конечно, насколько это правильно.

SELECT
    r.recipe_id,
    COUNT(*) AS catch_ingredients
FROM
    recipe_ingredients r
LEFT JOIN ingredients i ON i.id = r.ingredient_id
WHERE
	r.ingredient_id IN (77,88,99,100,33,444,55,20,21,22,23,24,25,26)
GROUP BY
    r.recipe_id
ORDER BY
    catch_ingredients
DESC

Стоп, а зачем тут вообще тогда left join, вроде и без него все считает как надо xD
Наверное, пора спать все таки.

[Akina]

а зачем тут вообще тогда left join, вроде и без него все считает как надо xD

Если удалить LEFT JOIN - получится строго то, что написано в ответе.

зачем тут вообще тогда left join

Если recipe_ingredients ( recipe_id , ingredient_id ) и ingredients ( id ) определены как уникальные - нафиг не нужен. Иначе - влияет.

Answer 2

[Rsa97]

Так какой вопрос то стоит?
"Найти рецепты, в которые входит хотя бы один ингредиент из списка?

SELECT `recipe_id`, `ingredient_id`
  FROM `recipe_ingredients`
  WHERE `ingredient_id` IN (501, 900)

"Рецепт можно приготовить из указанных ингредиентов"? Значит все ингредиенты данного рецепта присутствуют в заданном списке. То есть, общее количество ингредиентов рецепта равно количеству ингредиентов этого рецепта, присутствующих в списке.

SELECT `recipe_id`, COUNT(*) AS `count`,
    SUM(`ingredient_id` IN (501, 900)) AS `avail`
  FROM `recipe_ingredients`
  GROUP BY `recipe_id`
  HAVING `count` = `avail`

P.S.
А поле `id` в таблице лишнее. Достаточно составного первичного ключа (`recipe_id`, `ingredient_id`).