Блог

Это текущий дизайн базы данных SQL Server, с которой я работаю.

У меня есть два стола:

1. Рецепты

2. ингредиенты рецепта

Рецепты состоят из ингредиентов рецепта, но ингредиентом может быть другой рецепт. Теоретически существует бесконечное количество уровней, поскольку в каждом рецепте может быть другой ингредиент, который также является рецептом.

В приведенном выше примере данных рецепт свежей сальсы (ID 3047) состоит из 7 ингредиентов. Шесть — это сырье, но один — это другой рецепт (идентификатор рецепта 3008). Этот идентификатор рецепта ссылается на другой рецепт в таблице «рецепты».

Там нет иерархии, и я не думаю, что смогу создать иерархию.

Цель состоит в том, чтобы извлечь все элементы рецепта для конкретного рецепта, которые имеют «вспомогательные» рецепты и «вспомогательные» рецепты и т.д.

Казалось бы, рекурсивный поиск был бы ответом, но поскольку иерархии нет, это, похоже, не работает.

Вот моя попытка запроса (переменная списка RecipeItem — это список всех recipeitem, которые также являются рецептами, созданными в предыдущем запросе):

 <cfquery name="whatever">

WITH MenuPrepOfPreps (recipe_id, depth, otherRecipe_id, recipe_name)
AS

(
    SELECT r.recipe_id, 
    0 as depth,
    ri.otherRecipe_id,
    r.recipe_name 
    FROM menu_recipes r
    JOIN menu_recipeItems ri
        ON ri.otherRecipe_id = r.recipe_id
    WHERE ri.otherRecipe_id in (#recipeItemList#)
    UNION ALL
-- recursive members
    SELECT 
    mop.recipe_id,
    mop.depth   1 as depth,
    ri.otherRecipe_id,
    r.recipe_name
    FROM menu_recipes r
    JOIN menu_recipeItems ri
        ON ri.otherRecipe_id = r.recipe_id
    INNER JOIN MenuPrepOfPreps AS MOP
        ON ri.otherRecipe_id = MOP.recipe_id
        
)

SELECT top(6)recipe_id, recipe_name
FROM MenuPrepOfPreps
GROUP BY recipe_id, recipe_name

</cfquery>
 

Он продолжает создавать бесконечный цикл. Когда я ограничиваю результаты первыми несколькими строками (верхние 6), это дает желаемый результат.

Возможно, что дизайн базы данных неверен, поэтому это может никогда не сработать.

Любая помощь приветствуется.

[ОБНОВЛЕННЫЙ ЗАПРОС НА ОСНОВЕ ПРЕДЛОЖЕННОГО РЕШЕНИЯ @NewBie20200101 С ИЗМЕНЕНИЯМИ ИМЕН ПЕРЕМЕННЫХ / СТОЛБЦОВ]

 <cfquery name="whatever">

WITH MenuPrepOfPreps AS

(
    SELECT otherrecipe_id, 
    CASE 
        when 
            otherRecipe_id = 0 then null 
        else 
            otherRecipe_id 
        end 
        as sub_recipe 

    FROM menu_recipeItems as a -- anchor

    UNION ALL

    SELECT 
    a.otherrecipe_id,
    CASE 
        when
            b.otherRecipe_id = 0 then null 
        else
            b.otherRecipe_id 
        end 
        as sub_recipe
    FROM menu_recipeItems as b
    where b.recipe_id = a.otherRecipe_id --recursion
    and a.otherRecipe_id is null --stopper      
), allrecipeitems as (

SELECT recipe_id, sub_recipe
FROM MenuPrepOfPreps
)

Select
 c.recipe_id,
 d.otherRecipe_id
 From MENU_recipes c
 INNER JOIN MENU_recipeItems d on c.recipe_id = d.otherRecipe_id
 Where c.recipe in (#recipelist#)

</cfquery>
 

Не работает и выдает следующую ошибку:

Не удалось связать многосоставный идентификатор «a.otherRecipe_id».

Не уверен, что это сработает:

 With preppreprep as (
    Select     
        Recipeid,    
        Case when otherrecipeId = 0 then null else otherrecipeID end as otherrecipeID, ——remove 0 it might be a problem    
    From 
    Recipeitems as a ——————anchor    
     Left outer join recipeitems as b on a.otherrecipeID = b.recipeID
    Union all

    Select
        C.recipeid,    
        Case when c.otherrecipeID = 0 the null else c.other recipeID end as otherrecipeID, also remove 0    
    From preprepprep as c 
    Left outer join recipeitems as d
    Where c.recipeid = d.otherrecipeID———--recursion   
), allrecipeitems as (
    Select    
        RecipeID,
        OtherrecipeID
    From preprepprep    
)
Select
    C.RecipeID
    D.OtherRecipeID
From recipe c
Inner recipeitems d on c.recipeid = d.recipeid
Where c.recipe in (##)
——extract unpacked sub recipes based on recipe
 

Если вы считаете, что существует более 99 уровней, добавьте параметр max recursion 0

Я понял это сам с помощью всех участников (спасибо). Во-первых, как упоминал @Charlieface, я подходил к этому с неправильного направления. С помощью этого решения я начал с нижней части иерархии и работал до тех пор, пока в иерархии не осталось данных для получения.

Последний запрос содержит несколько фильтров и группировок, чтобы получить именно ту информацию, которая мне нужна.

Если кому-то интересно, вот решение:

 WITH MenuPrepOfPreps (recipe_id, otherRecipe_id, depth, recipe_name)

AS (
SELECT
    mria.recipe_id, mria.otherRecipe_id, 0, mr.recipe_name
FROM 
    menu_recipeItems mria, menu_recipes mr
where 
    mria.otherrecipe_id <> 0
    and mria.recipe_id = mr.recipe_id

UNION ALL

SELECT 
    MenuPrepOfPreps.recipe_id, mri.otherrecipe_id, MenuPrepOfPreps.depth 1, mr.recipe_name
FROM 
    menu_recipeItems mri, MenuPrepOfPreps, menu_recipes mr
WHERE MenuPrepOfPreps.otherrecipe_id = mri.recipe_id 
    AND MRI.otherrecipe_id <> 0
    AND mr.recipe_id = mri.recipe_id
)

SELECT 
    mopsA.otherrecipe_id, mopsA.recipe_id, mopsA.depth, mra.recipe_name AS thePrepRecipeName
FROM 
    MenuPrepOfPreps mopsA, MENU_recipes MRA
WHERE 
    mopsA.recipe_id in (#recipelist#)
    AND mopsA.otherRecipe_id = MRA.recipe_id 
    AND MRA.recipeType_id = 2
GROUP BY mra.recipe_name, mopsA.otherrecipe_id, mopsA.recipe_id, mopsA.depth
 

Мне нужно будет посмотреть, как это работает с большим набором данных, чем тот набор образцов, который у меня есть сейчас, но пока это происходит довольно быстро.

Я бы создал табличную функцию, которая возвращает весь лист recipeItem для одного recipe_id (что выравнивает иерархию), без рекурсии, но с циклом, который разрешает один уровень рекурсии за раз (вместо следования каждой отдельной ветви). Если у вас есть миллион вспомогательных рецептов глубиной до 10 уровней, для получения данных требуется 12 итераций (10 (глубина) 1 (определяет, что больше ничего не изменилось) 1 (чтобы получить все листья из рецептов).

Преимущество этого подхода также в том, что не нужно устанавливать никаких ограничений, если в базе данных определен бесконечный цикл, это нас нисколько не беспокоит.

Здесь приведен код для функции с табличным значением:

 CREATE FUNCTION [dbo].[GetRecipeItemsFlat](@recipe_id INT)
RETURNS @recipeItems TABLE (recipeItem_id INT NOT NULL)
AS
BEGIN
  DECLARE @recipeList TABLE (recipe_id INT);
  DECLARE @previousCount INT = 0;
  DECLARE @currentCount INT = 1;
  -- Get all recipe_ids recursively until infinity (no endless loop possible)
  INSERT INTO @recipeList SELECT @recipe_id;
  WHILE (@previousCount < @currentCount) BEGIN
    -- Adding not yet added child recipe_ids 
    INSERT INTO @recipeList SELECT DISTINCT otherRecipe_id FROM recipeItem WHERE recipe_id IN (SELECT recipe_id FROM @recipeList) AND (otherRecipe_id IS NOT NULL) AND (otherRecipe_id != 0) AND (otherRecipe_id NOT IN (SELECT recipe_id FROM @recipeList));
    SET @previousCount = @currentCount ;
    SET @currentCount = (SELECT COUNT(*) FROM @recipeList);
  END
  INSERT INTO @recipeItems SELECT recipeItem_ID FROM recipeItem WHERE recipe_id IN (SELECT * FROM @recipeList) AND (rawMaterial_id IS NOT NULL) AND (rawMaterial_id != 0);
  RETURN;
END
 

И тогда я бы создал представление, которое предоставляет весь лист recipeItem для recipe подобного:

 CREATE VIEW recipeItemFlat
AS
SELECT c.recipe_id, c.recipeItem_id, c.ItemQuantity, c.rawMaterial_id 
  FROM recipe a 
  CROSS APPLY dbo.GetRecipeItemsFlat(recipe_id) b 
  INNER JOIN recipeItem c ON b.recipeItem_id = c.recipeItem_id;
 

И тогда запрос на получение всех элементов для рецепта 3047 тривиален:

 SELECT * FROM recipeItemFlat WHERE recipe_id = 3047
 

Существует одна возможная проблема с этим решением, которое я обычно использую для разрешений прав и ролей и тому подобных вещей, где это не имеет значения, но здесь это может:

Если существует рецепт A, в котором есть два рецепта B и C, и оба B и C имеют рецепт D, то D будет только один раз в результате, и сложение количеств даст неверный результат. Если это важно, используйте не DISTINCT для определения конца цикла в функции с табличным значением, а максимальный уровень, как вы делали в своем примере.