Lazy upward recursive query in sql

Question

I'm trying to generate a path from the name of an item's parents. For example if test has for parent dad the path would be dad/test; and if dad had for parent gran the path of test would be gran/dad/test.

I only have the id of the child, so far I only have a query which generates the paths of everyone recursively and then selects the right one but that doesn't really seem efficient.

WITH    SubItems
AS (
    SELECT  CAST([Name] AS VARCHAR(255)) AS [Path],
        Id,
        ParentId,
        0 AS Depth
    FROM    Items
    WHERE   Id = 1 -- First parent of everyone
    UNION ALL
    SELECT  CAST(CONCAT(parent.[Path], '/', sub.[Name]) AS VARCHAR(255)),
        sub.Id,
        sub.ParentId,
        parent.Depth + 1
    FROM    Items sub
        JOIN SubItems parent ON parent.Id = sub.ParentId
)
SELECT      [Path]
FROM        SubItems
WHERE       Id = 1425 -- SubItem I want the path of

I can also go upwards, which would be faster but I can't create the path this way. I could try to concatenate all the results ordered by the "depth" but again this doesn't seem right.

DECLARE @Path;
WITH    ParentItems
AS (
    SELECT  [Name],
        Id,
        ParentId,
        0 AS Depth
    FROM    Items
    WHERE   Id = 1425 -- SubItem I want the path of
    UNION ALL
    SELECT  [Name],
        parent.Id,
        parent.ParentId,
        sub.Depth - 1
    FROM    Items parent
        JOIN  ParentItems sub ON sub.ParentId = parent.Id
)
SELECT      @Path = COALESCE(@Path + '/', '') + [Name]
FROM        ParentItems
ORDER BY    Depth;
SELECT @Path;

Is there a way to go upwards recursively? Something like this for example, where ParentPath would be equal to CONCAT(ParentPath, '/', [Path]) again:

WITH   ...
SELECT CONCAT(ParentPath, '/', [Name])
FROM   Items

I know in C# you could do something like:

function getPath() {
  return (parent?.getPath() ?? "") + "/" + this.Name;
}

---

Edit: Why I can't construct the path going up, like this:

WITH ParentItems AS (
      SELECT i.Name, i.Id, i.ParentId, 0 AS Depth,
             CONVERT(VARCHAR(MAX), i.Name) as path
      FROM Items i
      WHERE i.Id = 1425 -- SubItem I want the path of
      UNION ALL
      SELECT i.Name, i.Id, i.ParentId, pi.Depth - 1,
             CONCAT(pi.Name, '/', i.[Path])
      FROM Items i JOIN
           ParentItems pi
           ON pi.ParentId = parent.Id
     )
SELECT *
FROM ParentItems
ORDER BY Depth;

Assuming the example from above where gran is parent to dad is parent to test, the result of this query would be:

| name | path          |
|------|---------------|
| gran | gran/dad/test |
| dad  | dad/test      |
| test | test          |

While it should be the opposite:

| name | path          |
|------|---------------|
| gran | gran/         |
| dad  | gran/dad      |
| test | gran/dad/test |

This is because of the way the query passes the name of the child upwards, adding it to the path of its parent rather than the opposite.

Answer 1

Why can't you construct the path going up?

WITH ParentItems AS (
      SELECT i.Name, i.Id, i.ParentId, 0 AS Depth,
             CONVERT(VARCHAR(MAX), i.Name) as path
      FROM Items i
      WHERE i.Id = 1425 -- SubItem I want the path of
      UNION ALL
      SELECT i.Name, i.Id, i.ParentId, pi.Depth + 1,
             CONCAT(i.Name, '/', pi.path)
      FROM Items i JOIN
           ParentItems pi
           ON pi.ParentId = parent.Id
     )
SELECT *
FROM ParentItems
ORDER BY Depth DESC;

Answer 2

For future references, using FOR XML PATH('') seems to work.

WITH    ParentItems
AS (
    SELECT  [Name],
        Id,
        ParentId,
        0 AS Depth
    FROM    Items
    WHERE   Id = 1425 -- SubItem I want the path of
    UNION ALL
    SELECT  [Name],
        parent.Id,
        parent.ParentId,
        sub.Depth - 1
    FROM    Items parent
        JOIN  ParentItems sub ON sub.ParentId = parent.Id
)
SELECT (
    SELECT '/' + [Name]
    FROM ParentItems
    ORDER BY Depth
    FOR XML PATH('')
)

Answer 3

The following code:

1. Walks the tree from the child up to the oldest ancestor while assembling a path.
2. Gets the path to the oldest ancestor and splits it into individuals.
3. Walks the list of individuals from the oldest ancestor back down to the starting child while assembling the path.

NB: This code does not use String_Split because it is documented thusly: "The output rows might be in any order. The order is not guaranteed to match the order of the substrings in the input string." A Jeff Moden string splitter is used which guarantees the order of the results.

Note that you can select the results of any of the intermediate CTEs in order to see how the process proceeds. Just replace the final select statement with one of the alternatives provided in comments.

Confession: I didn't try to generate the curious dangling solidus in the first row of the desired output ("gran/") rather than the more consistent "gran". It is assumed to be a typographical error in the sample data.

-- Sample data.
declare @Samples as Table ( Id Int Identity, Name VarChar(10), ParentName VarChar(10) );
insert into @Samples ( Name, ParentName ) values
  ( 'test', 'dad' ),
  ( 'dad', 'gran' ),
  ( 'gran', null );
select * from @Samples;

-- Starting point.
declare @ChildName as VarChar(10) = 'test';

-- Walk the tree.
with
  Tree as (
    -- Note that paths in this initial tree are built using   Id , not   Name .
    --   This keeps the path length down, ensures rows are uniquely identified, avoids problems with "funny" names, ... .
    -- Start at the target child name.
    select Id, Name, ParentName, 0 as Depth,
      Cast( Id as VarChar(100) ) as Path
      from @Samples
      where Name = @ChildName
    union all
    -- Walk up the tree one level at a time.
    select S.Id, S.Name, S.ParentName, T.Depth + 1,
      Cast( Cast( S.Id as VarChar(100) ) + '/' + T.Path as VarChar(100) )
      from Tree as T inner join
        @Samples as S on S.Name = T.ParentName
    ),
  TreePath as (
    -- Take the path of the oldest ancestor and split it apart.
    select ItemNumber, Cast( Item as Int ) as Item from Tree as T cross apply
      dbo.DelimitedSplit8K( T.Path, '/' ) where T.ParentName is NULL ),
  InvertedTree as (
    -- Start at the first item on path, i.e. the oldest ancestor.
    select S.Name, 1 as Depth,
      Cast( S.Name as VarChar(100) ) as Path
      from TreePath as TP inner join
        @Samples as S on S.Id = TP.Item
      where TP.ItemNumber = 1
    union all
    -- Add chldren on the way down.
    select S.Name, IT.Depth + 1,
      Cast( IT.Path + '/' + S.Name as VarChar(100) )
      from InvertedTree as IT inner join
        TreePath as TP on TP.ItemNumber = IT.Depth + 1 inner join
        @Samples as S on S.Id = TP.Item
      )
  -- To see the intermediate results use one of the following   select   statements:
  --  select * from Tree;
  --  select * from TreePath;
  --  select * from InvertedTree;
  select Name, Path
    from InvertedTree
    order by Depth;

The Jeff Moden string splitter:

CREATE FUNCTION [dbo].[DelimitedSplit8K]
--===== Define I/O parameters
        (@pString VARCHAR(8000), @pDelimiter VARCHAR(16))
--WARNING!!! DO NOT USE MAX DATA-TYPES HERE!  IT WILL KILL PERFORMANCE!
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
     -- enough to cover VARCHAR(8000)
  WITH E1(N) AS (
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                ),                          --10E+1 or 10 rows
       E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
       E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
 cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
                     -- for both a performance gain and prevention of accidental "overruns"
                 SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
                ),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
                 SELECT 1 UNION ALL
                 SELECT t.N+ Len( @pDelimiter ) FROM cteTally t WHERE SUBSTRING(@pString,t.N, Len( @pDelimiter ) ) = @pDelimiter
                ),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
                 SELECT s.N1,
                        ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1 ,8000)
                   FROM cteStart s
                )
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
 SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
        Item       = SUBSTRING(@pString, l.N1, l.L1)
   FROM cteLen l
;