Структура запросов Postgres SQL / HQL, сравнивающая 2 списка

Question

У меня есть таблица FridgeContents, таблица рецептов и таблица ингредиентов рецепта.

CREATE TABLE Recipes
(
  id bigserial NOT NULL,
  name text
);
CREATE TABLE Ingredients
(
  id bigserial NOT NULL,
  name text,
  description text
);
CREATE TABLE RecipeIngredients
(
  id bigserial NOT NULL,
  recipe_id bigint REFERENCES Recipes(id),
  ingredient_id bigint REFERENCES Ingredients(id),
  quantity numeric
);
CREATE TABLE FridgeContents
(
  id bigserial NOT NULL,
  ingredient_id bigint REFERENCES Ingredients(id),
  quantity numeric
);

Я занимаюсь разработкой приложения SpringBoot с использованием Hibernate, Spring Data JPA и базы данных postgresql.

Я пытаюсь создать запрос, который бы возвращал список всех Рецептов, которые я мог сделать, учитывая мои текущие компоненты.

Одним из способов было бы вытащить все ингредиенты для рецепта в последовательности, отметить их в моем FridgeContents и вернуть true, если оставшаяся часть списка RecipeIngredients пуста, но это звучит неэффективно, особенно если у меня было 1000 рецептов.

Будет ли что-то вроде этой работы, и есть ли какой-нибудь лучший трюк, который я мог бы использовать?

Select * from Recipes where id in (
  Select distinct recipe_id from RecipeIngredients EXCEPT (
    Select distinct recipe_id from RecipeIngredients
    where ingredient_id NOT in (
      Select ingredient_id from FridgeContents
    )
  )
);

Answer 1

Я хотел бы подойти к этому с помощью объединений и агрегации:

select ri.recipe_id
from recipeingredients ri left join
     fridgecontents fc
     on ri.ingredient_id = fc.ingredient_id
group by recipe_id
having count(*) = count(fc.ingredient_id);

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

having count(distinct ri.ingredient_id) = count(distinct fc.ingredient_id)

Answer 2

Двойной NOT EXISTS() конструкция для реляционного деления. Примечание: результат также будет включать рецепты без ингредиентов вообще...

SELECT * 
FROM recipes r
        -- ## You cannot make a recipe
WHERE NOT EXISTS (
        SELECT *
        FROM recipe_ingredients ri
        WHERE ri.recipe_id = r.id
        -- ## ... for which any of the ingredients
        -- ## ... is *NOT* available
        AND NOT EXISTS ( 
                SELECT * FROM fridgecontents fc
                WHERE fc.ingredient_id = ri.ingredient_id
                AND fc.quantity >= ri.quantity
                )
        );