CODEWITHSUNDEEP
sqlruby-on-railspostgresql
Volkan
Volkan

Reputation: 546

SQL join to narrow down result set

I have a simple Ruby on Rails app CookBook, with recipes. I have 2 tables (recipes and ingredients). One recipe has many ingredients.

I am trying to implement a simple search box so that I can filter recipes containing certain ingredients....

Here is my issue: if I type in search box two or more ingredients, I get more rows, but I think the result set should be shorter, because the more ingredients I type in the search box, the narrower my search should be....

These are the tables:

create table recipes (id int, recipe_name varchar(100));

create table ingredients(id int, ingredient_name varchar(100), recipe_id int);

insert into recipes values 
(1, 'my recipe 1'),
(2, 'my recipe 2');
(3, 'my recipe 3');
(4, 'my recipe 4');

insert into ingredients values
(1, 'Banana', 1), 
(2, 'Milk', 1),
(3, 'Banana', 2), 
(4, 'Milk', 2),
(5, 'Apple', 2);
(6, 'Flour', 4),
(7, 'Lemon', 4);
(8, 'Lemon', 3),
(9, 'Milk', 3);
(10, 'Banana', 4);

Let's say I type in search box two ingredients, for example "banana" and "milk". Then I am expecting to get recipes with id 1 and 2 in my result set, because both of those recipes contain 'banana' and 'milk' However, there is a recipe id 4 with an ingredient 'banana'. Should it be displayed too?

This is the query I currently have:

SELECT DISTINCT "recipes".* 
FROM "recipes" 
LEFT  JOIN "ingredients" ON 
"ingredients"."recipe_id" = "recipes"."id" 
WHERE (ingredient_name LIKE '%banana%' OR ingredient_name LIKE '%milk%')

The problem with this query is that if I type more ingredients in search box, the result set becomes larger. What I probably need is to display a recipe that contains all the ingredients given. Not every recipe that contains either one of these ingredients...

If I search for only one ingredient, like "milk" then I should get all the recipes containing that ingredient.

If I type two or more keywords in a search box, like "milk banana", then I should get a shorter result set, displaying only recipes that contain both of these ingredients.

Is this logic wrong? I mean, the more search keywords then the result set should become narrower, I assume. What is the correct query for this?

Thank you in advance

Upvotes: 0

Views: 187

Answers (2)

luisvenezian
luisvenezian

Reputation: 501

-- PostgreSQL 14.2

create table recipes (id int, recipe_name varchar(100));

create table ingredients(id int, ingredient_name varchar(100), recipe_id int);

insert into recipes values 
(1, 'my recipe 1'),
(2, 'my recipe 2');

insert into ingredients values
(1, 'Banana', 1), 
(2, 'Milk', 1),
(3, 'Banana', 2), 
(4, 'Milk', 2),
(5, 'Apple', 2);

-- Lets search for only the recipe with both ingredients: Banana and Milk
-- expecting to see only the recipe number 1 and their ingredients
create temporary table desired (ingredient_name varchar(100));
insert into desired values ('Banana'), ('Milk');

select 
    recipe_name, i.ingredient_name
from recipes r
    inner join ingredients i on r.id = i.recipe_id
    left  join (
        select recipe_id, count(*) qt_ingredient from ingredients group by 1
    ) qt on qt.recipe_id = r.id
where ingredient_name in (select ingredient_name from desired)
and qt.qt_ingredient=(select count(*) from desired);

-- although if you want to see every recipe that contains these two ingredients you could try:
with desired_recipes as (
    select recipe_id 
    from ingredients i
    inner join desired d 
    on d.ingredient_name = i.ingredient_name -- change for id if possible
    group by 1
)
select i.*
from desired_recipes dr
    left join ingredients i on i.recipe_id = dr.recipe_id;

Upvotes: 1

NickW
NickW

Reputation: 9768

The logic in your join defines how it will join to a single row. As there is no record in the ingredient table that has both values then the AND approach won’t work.

You would need to write a query that:

  1. Has an OR in the join, counts the number of matching records and filters where the count is 2 - or however many ingredients you want to match
  2. Has a join for each ingredient and a filter where the columns from all joins are not null
  3. Has an EXISTS statement for each ingredient, all AND-ed together

Upvotes: 1

Related Questions