Mysql Question posted in
The official MySQL documentation can be found here.

Relational division against values rather than tables MySQL

I’m writing a simple program to find what cocktails somebody is able to make given only the ingredients that they already have on hand. The schema looks something like this:

Table CocktailIngredients

cocktailId ingredientId
0 0
0 2
1 0
1 1

Table UserIngredients

ingredientId
0
1
2

I use a relational division to figure out what cocktails the user can make with their current ingredients. However, I don’t like having to add the users ingredients to the DB, especially when they are more than likely logged in as a guest and won’t use the ingredients in the DB again, causing them to be deleted and use write cycles. My question is, is there a way to replace the UserIngredients table by just manually inputting as many ingredients into the query as necessary?

For context, here is my current SQL query:

SELECT cocktailId 
FROM CocktailIngredients a
JOIN UserIngredients b ON a.ingredientId = b.ingredientId
GROUP BY cocktailId
HAVING COUNT(*) = (
    SELECT COUNT(*) FROM CocktailIngredients c
    WHERE c.cocktailId = a.cocktailId
)
Tags:

2

Answers


  1. 0

    Here is one option to do it …

    WITH    --  S a m p l e    D a t a :   
  coctail_ingredients ( id, ingredient_id, ingredient ) As  -- |1 Martini|2 Bucks Fizz|3 Gin Sour|
    ( Select 1, 0, 'Gin' Union All
      Select 1, 1, 'Vermouth' Union All
      Select 1, 2, 'Lemon' Union All
      --
      Select 2, 3, 'Orange' Union All
      Select 2, 4, 'Champagne' Union All
      --
      Select 3, 0, 'Gin' Union All
      Select 3, 2, 'Lemon' Union All
      Select 3, 5, 'Syrup' Union All
      Select 3, 6, 'Egg'
    ),

    Supply user selected ingredient ids as a comma sparated list – here the list is declared as a cte

      user_ingredients As
    ( Select '0,1,2,6' as user_ingredient 
    ),

    Cross Join your table data with user selected list – use Case expressions with Find_In_Set() function to calculate percentage of user ingredients vs total ingredients and get missing ingredient ids.

      grid as 
    ( Select     c.id, c.ingredient_id, c.ingredient, 
                 -- 
                 Count( Case When Find_In_Set(c.ingredient_id, u.user_ingredient) > 0
                             Then c.ingredient_id 
                        End
                      ) Over(Partition By c.id) 
                 * 100 / 
                 Count(c.id) Over(Partition By c.id) as pct_match,
                 --
                 Case When Find_In_Set(c.ingredient_id, u.user_ingredient) = 0
                      Then c.ingredient_id 
                 End as missing
      From       coctail_ingredients c
      Cross Join user_ingredients u 
    )

    … now you can propose the coctails to the user either just those with 100% matches or, for instance, 50% oor more along with the list of missing ingredients

    --    M a i n    S Q L : 
Select  
        id as cocktail_id, pct_match, 
        Group_Concat(missing separator ', ') as missing_ingredients
From    grid
Where   pct_match >= 50
Group By id, pct_match

    /*      R e s u l t : 
cocktail_id  pct_match  missing_ingredients
-----------  ---------  -------------------
          1   100.0000                 null
          3    75.0000                    5    */
    Login or Signup to reply.
  2. 0

    You can put UserIngrediets as parameters

    where ingredientId in(0,1,2)

    See example.

    Near classic relational division

    select distinct CocktailId
from CocktailIngredients ci
where not exists
  (select ingredientId from CocktailIngredients ci2
   where ci2.CocktailId=ci.CocktailId and ingredientId not in(0,1,2) )

    Or the remainder of the division

    select distinct CocktailId
from CocktailIngredients ci
where exists
  (select ingredientId from CocktailIngredients ci2
   where ci2.CocktailId=ci.CocktailId and ingredientId not in(0,1,2) )

    More practical examples.
    Test data:

    cocktailId ingredientId
    0 0
    0 2
    1 0
    1 1
    2 0
    2 1
    2 2
    3 0
    3 2
    3 3 
    select cocktailId,count(*)availibleCount,max(ingCount)needCount
from(
   select *
      ,count(*)over(partition by cocktailId)ingCount
   from CocktailIngredients
  )t
where ingredientId in(0,1,2)
group by cocktailId
    cocktailId availibleCount needCount
    0 2 2
    1 2 2
    2 3 3
    3 2 3

    Or

    select cocktailId,sum(availible)availibleCount,count(*)needCount
from(
   select *
      ,case when ingredientId in(0,1,2) then 1 else 0 end availible
   from CocktailIngredients
  )t
group by cocktailId

    OR simplest case

    select cocktailId 
  ,group_concat(ingredientId)CocktailIngredients
  ,group_concat(case when ingredientId in(0,1,2) then null else ingredientId end)MissingIngredients
from CocktailIngredients
group by cocktailId
    cocktailId CocktailIngredients MissingIngredients
    0 0,2 null
    1 0,1 null
    2 0,1,2 null
    3 0,2,3 3

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search