Return nested data from recursive query

Question

We have a list of recipes (recipe_id) and ingredients (ingredient_id). Every recipe can become an ingredient of another recipe and that child recipe can then hold more second level child recipes and so on. I need a recursive query that takes a recipe id (or an array of ids - how I have it setup now) and returns a NESTED table or array of all the ingredients of the main recipes and any child recipes.

We're using Postgresql / Supabase / PostgREST.

So far I've been able to create the recursive query as a RPC function that returns a table using UNION ALL. This gives me a flat table back and I can't definitively trace back an ingredient to a specific parent recipe (because the same recipe can be called as a child in multiple parent recipes). Not sure where to go from here? The only other option I've figured out so far is to have my API endpoint query each level one at a time, but it generates a lot of network requests =(

DESIRED OUTPUT Super flexible on format, but it would be nice if I could get all the child components as a nested array like so:

[
  { id: 1,
    recipe_id: 22,
    item_id: 9,
    item: "Croissant Dough",
    ...,
    components: [
      { id: 2,
        recipe_id: 1,
        item_id: 33,
        item: "Butter,
        ...,
        components: []
      },
      { id: 3,
        recipe_id: 1,
        item_id: 71,
        item: "Wheat Flour",
        ...,
        components: []
      }
    ]
  },
  { id: 1,
    recipe_id: 29,
    item_id: 4,
    item: "Almond Filling",
    ...,
    components: [
      { id: 2,
        recipe_id: 29,
        item_id: 16,
        item: "Almond Meal,
        ...,
        components: []
      },
      { id: 3,
        recipe_id: 29,
        item_id: 42,
        item: "Pastry Cream",
        ...,
        components: [
          { id: 7,
            recipe_id: 42,
            item_id: 22,
            item: "Egg Yolks",
            ...,
            components: []
        ]
      }
    ]
  },
]

CURRENT RPC FUNCTION

CREATE or REPLACE FUNCTION recipe_components_recursive (recipeids text)
RETURNS TABLE (id int8, recipe_id int8, item_id int8, quantity numeric, unit_id int8, recipe_order int4, item text, visible bool, recipe bool, "unitName" varchar, "unitAbbreviation" varchar, "conversionFactor" float4, "metricUnit" int8, batch bool)
LANGUAGE plpgsql 
AS $$
DECLARE 
    transformedjson int[] := recipeids;
BEGIN
    RETURN QUERY
    WITH RECURSIVE recipe_components_rec_query AS (
        SELECT *
        FROM recipe_components_items
        WHERE recipe_components_items.recipe_id = ANY (transformedjson)
        UNION ALL
            SELECT o.id, o.recipe_id, o.item_id, o.quantity, o.unit_id, o.recipe_order, o.item, o.visible, o.recipe, o."unitName", o."unitAbbreviation", o."conversionFactor", o."metricUnit", o.batch
            FROM recipe_components_items o
            INNER JOIN recipe_components_rec_query n ON n.item_id = o.recipe_id AND n.recipe = true
    ) SELECT *
        FROM recipe_components_rec_query;
END $$;

Return nested data from recursive query

0 Answers0