SQL Server 2014 Nested Blog comments sorting

Question

I'm trying to build a small blog allowing people to add comments..

For comments I prepared a table like this:

ID  PostID  ParentID    Date    Title   Text    User    ....
1      1        0       
2      2        0
3      1        0
4      1        1
5      1        1
6      1        4
7      1        1
8      1        5
9      1        8

where if ParentID is not 0 means that this comment is not referred to the post but to the comment with that ID (is nested)

ie: assuming to select Post 1 will extract all comments except ID=2.

• we have posts 1 and 2 on the root
• posts 4,5,7 are nested in comment 1
• post 6 is nested in 4 (triple nesting)
• post 8 is nested in 5 (triple nesting)
• post 9 is nested in 8 (quadruple nesting)

Therefore I expect the query returns the record in this order:

1
  4
    6
  5
    8
      9
  7
2

But I'm struggling with the query to achieve the above result.

Actually, for single nesting I find a solution, but for deeper nesting.. have really no idea..

I also thought that table schema used is not adequate..

On the other side I thought that such issue can be a common issue, but google didn't gave me any valuable result.. probably used wrong keywords..

Can you please suggest a path to solve this problem, or where to search for?

Thanks!
Jan

Answer 1

the solution is in Eric Weilinau's answer at this Post.

Basically it uses a recurring CTE
I modified a little to use ID value as Order and adding a further filter.
Here is the query:

WITH [NodeList] (
  [Id], [ParentId], [Level], [Order]
) AS (
  SELECT [Node].[Id]
    , [Node].[ParentId]
    , 0 AS [Level]
    , Right('0000'+CONVERT([varchar](MAX), [Node].[ID]),4) AS [Order]
  FROM [Node]
  WHERE [Node].[ParentId] = 0 
  UNION ALL
  SELECT [Node].[Id]
    , [Node].[ParentId]
    , [NodeList].[Level] + 1 AS [Level]
    , [NodeList].[Order] + '|'
      + Right('0000' + CONVERT([varchar](MAX), [Node].[ID]),4) AS [Order]
  FROM [Node]
    INNER JOIN [NodeList] ON [NodeList].[Id] = [Node].[ParentId]
) SELECT [NodeList].[id], 
        [NodeList].[Level], 
        REPLICATE('-', [NodeList].[Level] * 4) + [Node].[Name] AS [Name]
FROM [Node]
  INNER JOIN [NodeList] ON [NodeList].[Id] = [Node].[Id]
  WHERE BlogID=1
ORDER BY [NodeList].[Order]

if somebody can find it helpful.