November 15, 2024 at 8:26 pm
I've read lots of the horror stories about nested views in databases where they're like 7 levels deep... hence the question.
I'm working on the bakery thing (well, because parts of the data problem are interesting). In this case, I'm looking at the recipe where some ingredients are sort of "subassemblies". In this case, the recipe will list all the ingredients and then list 500 grams of "Starter A" as an ingredient, for example. In order to get the "base ingredients" for a recipe, I created a view because I wanted to basically use it like a table, because it basically encapsulates the ugly logic of the scaling and substitutions. It looks like this <cringe>:
ALTER VIEW [dbo].[vwBaseIngredientRecipe]
/* NOTES:
The idea here is to split the Starter from the rest of the recipe
because Starter has its own "sub-recipe" (Potato flakes, flour, water for the most part)
This is necessary to break the ingredient list all the way down to just raw
ingredients.
Once that's done, get the sum of the weight of each raw ingredient (for pricing)
*/AS
WITH cteRecipe(RecipeID, RecipeName, IngredientID, IngredientName, RecipeWeight, Yield)
/* NOTES:
The idea here is to split the Starter from the rest of the recipe
because Starter has its own "sub-recipe" (Potato flakes, flour, water for the most part)
This is necessary to break the ingredient list all the way down to just raw
ingredients.
Once that's done, get the sum of the weight of each raw ingredient (for pricing)
*/AS (
SELECT
r.RecipeID
,r.[Recipe Name]
,i.IngredientID
,i.IngredientName
,ExpandedRI.RecipeIngredientWeight
,r.Yield
FROM
/* non-Starter ingredients */(SELECT ri.RecipeID
,ri.IngredientID
,i.IngredientName
,ri.RecipeIngredientWeight
FROM RecipeIngredient ri
INNER JOIN Ingredient i ON ri.IngredientID = i.IngredientID
WHERE /* ri.RecipeID = @RecipeID
AND */ i.IngredientName NOT LIKE '%Starter%'
UNION ALL
/* Starter Ingredients */SELECT ri.RecipeID
,sr.IngredientID
,ri.RecipeIngredientWeight * sr.WeightGrams/
(1.000 * SUM(sr.WeightGrams) OVER (PARTITION BY ri.RecipeID))
ri.RecipeIngredientWeight * sr.WeightGrams/(1.000 * SUM(sr.WeightGrams) OVER (PARTITION BY ri.RecipeID))
FROM RecipeIngredient ri
INNER JOIN Ingredient i ON ri.IngredientID = i.IngredientID
INNER JOIN StarterRecipe sr ON i.IngredientID = sr.ParentIngredientID
WHERE /* ri.RecipeID = @RecipeID
AND */ i.IngredientName LIKE '%Starter%') ExpandedRI
INNER JOIN Recipe r
ON ExpandedRI.RecipeID = r.RecipeID
INNER JOIN Ingredient i
ON ExpandedRI.IngredientID = i.IngredientID
)
SELECT
cte.RecipeID
,cte.RecipeName
,cte.IngredientID
,cte.IngredientName
,cte.RecipeWeight
,SingleLoafIngredientWeightGrams = cte.RecipeWeight/(1.000 * r.Yield)
,r.Yield
FROM cteRecipe cte
INNER JOIN Recipe r
ON r.RecipeID = cte.RecipeID;
The idea is that I can then join that to various other tables etc to create things like Ingredient Cost summaries (join to "most recent price" query in a CTE or whatever).
So at what point is having nested queries okay vs too much? That view is always close to top level - never more than 2 levels down. At what point does this conflict with the Don't Repeat Yourself adage? (by basically copying and pasting the definition of the view into different queries). For example, if I wanted to get cost details for a recipe, I'd join the Recipe (RecipeID, IngredientID, Yield) and a "latest price as of date" query to get the current price (just P*Q).
Basically, I reuse the query all over the place because it's instrumental in answering so many other questions.
Thanks!
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply