Using Common Table Expressions to explode recipes - Part 2 - Getting the ingredients
A Brief Recap
In the last part of this article I showed a table structure for recipes and their ingredients. There are two kinds of ingredients that a recipe can contain, raw ingredients (e.g. Lasagne Sheets) and other recipes (e.g. White Sauce). This allows your recipes to share combinations of ingredients that are common without having to specify the components and quantities in each recipe, e.g. both the Lasagne and the Macaroni Cheese recipes can contain the White Sauce recipe instead of each having to contain quantities of flour, butter and milk. I showed how you can use a recursive CTE to determine what recipes are inside each recipe, even when you have a recipe that contians a recipe that contains a recipe, i.e. no matter how many levels of inclusion there are. This means that the CTE works just as well if you have a recipe that contains another recipe as it does if you have that but repeated for several levels down.
So, what's left to do? Well we now know which recipes are contained within each other recipe, but that doesn't tell us what the constituent raw ingredients of a recipe are so that's the next query that we need to build up.
- Using Common Table Expressions to explode recipes - Part 1 - The CTE
- Using Common Table Expressions to explode recipes - Part 2 - Getting the Ingredients (this part)
Getting the Ingredients
The first thing to do is to take a look at the CTE that recursively resolves the recipe contents of each other recipe by joining the Recipe_Recipe_Ingredient table upon itself:
;WITH RecipeItems AS ( SELECT ParentRecipeId, RecipeId, Quantity FROM [Recipe_Recipe_Ingredient] RRI UNION ALL SELECT RI.ParentRecipeId, RRI.RecipeId, CAST(RI.Quantity * RRI.Quantity AS DECIMAL(10, 3)) FROM [Recipe_Recipe_Ingredient] RRI INNER JOIN RecipeItems RI ON RRI.ParentRecipeId = RI.RecipeId ) SELECT * FROM RecipeItems
This shows us the recipes that are ingredients of each other recipe, along with the fraction of the recipe that goes into it:
We've still only got recipes here though, there's no ingredients being bought into play so we need to do that.
There are two parts to this query, the first is getting the ingredients that are contained in each recipe that's used as an ingredient and the second is getting any ingredients that are contained in each recipe that isn't. This is because, in the example of the 'Lasagne' recipe it contains the recipes Lasagne Bolognese and White Sauce but also contains the raw ingredients Mozarella, Lasagne Sheet, etc,.. We need to know what its ingredients are both ways. Here's a query that gives us that:
SELECT RI.ParentRecipeId [ExplodedRecipeId], RI.Quantity [RecipeQuantity], RII.Quantity [IngredientQuantity], RII.IngredientId FROM RecipeItems RI LEFT JOIN dbo.Recipe_Ingredient_Ingredient RII ON RI.RecipeId = RII.ParentRecipeId UNION ALL SELECT ParentRecipeId, 1.00, Quantity, IngredientId FROM Recipe_Ingredient_Ingredient WHERE ParentRecipeId IN (SELECT DISTINCT ParentRecipeId FROM RecipeItems)
The first query in the union uses the CTE to say "for every (child) recipe that's inside another (parent) recipe, give the raw ingredients of the (child) recipe as they form part of that parent recipe". The second part of the union again uses the CTE, but only to get a distinct list of parent recipes, and gives the raw ingredients for each of those recipes that haven't already been included in the resultset. This means that, for the Lasagne recipe the first part of the query gives all the ingredients that are pulled in by virtue of being part of a recipe ingredient and the second part of the query brings in all the raw ingredients that are directly contained by the Lasagne recipe. Here's the output for that recipe with an additional column added to show which part of the query the ingredient came from and what the name of the ingredient is:
That's still not much SQL altogether compared to what would have to have been written to denormalise the recipes before CTEs were available. The last thing to do is to put it all together and get some "human readable" results.
Putting it all together
This is quite a long chunk of SQL because I'm using a temporary table to make the overall query easier to follow, rather than nested sub-queries, but it does show all the various bits of SQL I've talked through in use together:
DECLARE @ExplodedRecipeItems TABLE ( ExplodedRecipeId INT NOT NULL, RecipeQuantity DECIMAL(10, 2), IngredientQuantity DECIMAL(10, 2), IngredientId INT ) ;WITH RecipeItems AS ( SELECT ParentRecipeId, RecipeId, Quantity FROM [Recipe_Recipe_Ingredient] RRI UNION ALL SELECT RI.ParentRecipeId, RRI.RecipeId, CAST(RI.Quantity * RRI.Quantity AS DECIMAL(10, 3)) FROM [Recipe_Recipe_Ingredient] RRI INNER JOIN RecipeItems RI ON RRI.ParentRecipeId = RI.RecipeId ) INSERT INTO @ExplodedRecipeItems ( ExplodedRecipeId, RecipeQuantity, IngredientQuantity, IngredientId ) SELECT RI.ParentRecipeId [ExplodedRecipeId], RI.Quantity [RecipeQuantity], RII.Quantity [IngredientQuantity], RII.IngredientId FROM RecipeItems RI LEFT JOIN dbo.Recipe_Ingredient_Ingredient RII ON RI.RecipeId = RII.ParentRecipeId
UNION ALL SELECT ParentRecipeId, 1.00, Quantity, IngredientId FROM Recipe_Ingredient_Ingredient WHERE ParentRecipeId IN (SELECT DISTINCT ParentRecipeId FROM RecipeItems) SELECT R.Description, SQ.IngredientUsageCount, SQ.Quantity, I.Description + ' (' + I.UnitOfMeasure + ')' [Ingredient] FROM ( SELECT ExplodedRecipeId, COUNT(1) IngredientUsageCount, SUM(IngredientQuantity * RecipeQuantity) [Quantity], IngredientId FROM @ExplodedRecipeItems GROUP BY ExplodedRecipeId, IngredientId ) SQ INNER JOIN dbo.Recipe R ON SQ.ExplodedRecipeId = R.RecipeId INNER JOIN dbo.Ingredient I ON SQ.IngredientId = I.IngredientId ORDER BY ExplodedRecipeId
The last thing to do is to show some of the results from this query and give a brief explanation of what's shown in it, so here's part of the result for the Lasagne recipe:
This is showing some of the ingredients that are required to make a Lasagne, without recourse to each constituent part (so it's basically looking at the summary list of ingredients at the top of a recipe in a cook book rather than the quantities/components needed for each sub-part of the recipe). If you look at the IngredientUsageCount column you can see that Large White Onion is used twice, which is because one onion is used to make the Lasagne Bolognese and half an onion is used to make the white sauce, for a grand total of one and a half onions. The only thing you may want to note is that the queries, as written won't show you the ingredients of recipes that only contain recipes (like White Sauce and Lasagne Bolognese) as they're essentially "containers" for ingredients for other recipes.