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.

Other Parts

• 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.