cocktail builder (cocktailbuilder) wrote,
cocktail builder

The Query to Power It All

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:

    FORMAT(round((AVG(rating.Rating)*2))/2, 1) AS Rating, 
    (CountPresent - CountMissing) as Difference 
        cocktail.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 CountPresent 
            mix INNER JOIN ingredient 
                ON mix.IngredientID = ingredient.ID 
            LEFT JOIN (
                SELECT NormalizedIngredientID 
                FROM ingredient 
                WHERE ID=76 
                SELECT NormalizedIngredientID 
                FROM ingredient 
                WHERE ID=73) AS userHas 
                ON userHas.NormalizedIngredientID =
            INNER JOIN cocktail ON mix.CocktailID = cocktail.ID 
            WHERE NOT (cocktail.Status = 'Cut') 
            GROUP BY mix.CocktailID) AS InnerCountsTable 
LEFT JOIN rating 
    ON InnerCountsTable.CocktailID = rating.CocktailID 
WHERE CountPresent > 0 
GROUP BY InnerCountsTable.CocktailID 
    CountMissing 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?


  • QCubed: An Excellent PHP Framework

    I haven't been writing about it on this blog, but I've been intimately involved in the development of one of the best PHP5 frameworks out there. The…

  • Another award and an interview!

    Cocktail Builder won another award! SEOMoz gave the site a Gold Medal in the Web 2.0 Fun Stuff category again this year! And hey, they even asked…

  • Looking for a designer

    Cocktail Builder needs a talented graphic designer to take the site to the next level. Do you like the site and think you can make it even better?…

  • Post a new comment


    default userpic
    When you submit the form an invisible reCAPTCHA check will be performed.
    You must follow the Privacy Policy and Google Terms of use.