Rel8Experiment.hs

-- SELECT to_json(cc)
-- FROM (
--   SELECT
--     c.name,
--     COALESCE(array_agg(i) FILTER(WHERE i.name IN ('gin')), '{}') AS matches,
--     COALESCE(array_agg(i) FILTER(WHERE i.name NOT IN ('gin')), '{}') AS missing
--   FROM cocktails as c
--   JOIN ingredient_use as iu ON iu.cocktail_id = c.id
--   JOIN ingredients as i ON i.id = iu.ingredient_id
--   GROUP BY c.id, c.name
-- ) as cc
-- WHERE cardinality(cc.missing) < 4
-- ORDER BY
--   cardinality(cc.missing) ASC,
--   cardinality(cc.matches) DESC

cocktailIngredients cocktail = do
  use <- each ingredientUseSchema
  ingredient <- each ingredientSchema
  where_ (cocktailId cocktail ==. useCocktailId use)
  where_ (ingredientId ingredient ==. useIngredientId use)
  return ingredient

getByIngredients
  :: Int64
  -> Int64
  -> [Text]
  -> Statement () [(Cocktail Result, [Ingredient Result], [Ingredient Result])]
getByIngredients minMatches maxMissing ingredients= select do
  cocktail <- each cocktailSchema
  ingredient <- cocktailIngredients cocktail
  let matchingQ = filter ((`in_` map lit ingredients) . ingredientName) ingredient
      missingQ = filter (not_ . (`in_` map lit ingredients) . ingredientName) ingredient
  missingCount <- countRows missingQ
  matchingCount <- countRows matchingQ
  where_ $ (>=. lit minMatches) matchingCount
         &&. (<=. lit maxMissing) missingCount
  let output = (cocktail,,) <$> many matchingQ <*> many missingQ
  orderBy (mconcat [const missingCount >$< asc, const matchingCount >$< desc]) output