This article is only meant for major, major SQL geeks. You'll be bored out of your mind if you're not one; don't tell me I didn't warn you.
As you might have guessed, there's one, only one query powering the basic cocktail builder functionality. It's the query that answers the question "if I have ingredients A, B, and C, give me a list of all cocktails that I can make". Also:
- give me cocktails that I can almost make, ordered by the number of missing ingredients (i.e. those that I can make right away go first, those that are missing a single ingredient second, etc).
- consider ingredient substitutions (a concept I refer to as "normalized ingredients"): if you have Sky Vodka, and the recipe calls for Stoli, you can make the cocktail just fine.
- calculate cocktail ratings from the user feedback
As you might guess, the SQL is rather involved. Here's the statement that gets executed when the user has two ingredients - items with ID's 73 and 76:
SELECTInnerCountsTable.CocktailID,InnerCountsTable.CocktailName,InnerCountsTable.CocktailShortName,CountPresent,CountMissing,FORMAT(round((AVG(rating.Rating)*2))/2, 1) AS Rating,(CountPresent - CountMissing) as DifferenceFROM(SELECTcocktail.ID as CocktailID,cocktail.Name as CocktailName,cocktail.ShortName as CocktailShortName,cocktail.Instructions as CocktailInstructions,SUM(IF(userHas.NormalizedIngredientID IS NULL, 1, 0))as CountMissing,SUM(IF(userHas.NormalizedIngredientID IS NULL, 0, 1))as CountPresentFROMmix INNER JOIN ingredientON mix.IngredientID = ingredient.IDLEFT JOIN (SELECT NormalizedIngredientIDFROM ingredientWHERE ID=76UNIONSELECT NormalizedIngredientIDFROM ingredientWHERE ID=73) AS userHasON userHas.NormalizedIngredientID =ingredient.NormalizedIngredientIDINNER JOIN cocktail ON mix.CocktailID = cocktail.IDWHERE NOT (cocktail.Status = 'Cut')GROUP BY mix.CocktailID) AS InnerCountsTableLEFT JOIN ratingON InnerCountsTable.CocktailID = rating.CocktailIDWHERE CountPresent > 0GROUP BY InnerCountsTable.CocktailIDORDER BYCountMissing ASC,Difference DESC,CountPresent DESC LIMIT 30
A weird part of the query is the "UNION" part inside (the one that's creating the userHas part). Basically, I don't believe there's any other way to create an in-memory virtual table in MySQL to do a JOIN with. Anyone got better ideas?
Or, maybe you can offer a completely different approach that will work faster?