CODEWITHSUNDEEP
sqlsql-server
jamheadart
jamheadart

Reputation: 5323

SQL recursion on a self-referencing table to obtain specific order

I have a table as below:

Id LinkSlug ParentPageId Order
1 home 0
2 page2 1
3 page3 2
4 page11 1 0
5 page12 1 1
6 page13 1 2
7 page21 2 0
8 page22 2 1
9 page121 5 0
10 page122 5 1
11 page123 5 2

I'm sure you can already see the pattern - each Page can have any number of "SubPages" defined by the ParentPageId

I've been trying to get a query that can produce the following ordered output (without using the LinkSlug alphabetical ordering because they can be anything):

Id LinkSlug ParentPageId Order
1 home 0
4 page11 1 0
5 page12 1 1
9 page121 5 0
10 page122 5 1
11 page123 5 2
6 page13 1 2
2 page2 1
7 page21 2 0
8 page22 2 1
3 page3 2

I tried doing some self-joins and grouping but ended up with only one level of recursion so it was no good for the 3rd and potentially nth degree sub pages, and then also tried using a CTE as I understand they're good for recursive queries but somehow ended up producing the same table I started with and am now at a loss!

The more I try the worse it gets - I know I need to effectively select top levels (with null ParentPageId) ordered by [Order], then inject wherever there's sub pages ordering by [Order], and repeat until there are no children left - but no idea how to do this in SQL.

View on DB Fiddle

And here's the fiddle script just in case:

CREATE TABLE [Pages](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [LinkSlug] [nvarchar](450) NOT NULL,
    [ParentPageId] [int] NULL,
    [Order] [int] NOT NULL
);

INSERT into [Pages] ([Id], [LinkSlug], [ParentPageId], [Order]) VALUES (1, 'home', NULL, 0);
INSERT into [Pages] ([Id], [LinkSlug], [ParentPageId], [Order]) VALUES (2, 'page2', NULL, 1);
INSERT into [Pages] ([Id], [LinkSlug], [ParentPageId], [Order]) VALUES (3, 'page3', NULL, 2);
INSERT into [Pages] ([Id], [LinkSlug], [ParentPageId], [Order]) VALUES (4, 'page11', 1, 0);
INSERT into [Pages] ([Id], [LinkSlug], [ParentPageId], [Order]) VALUES (5, 'page12', 1, 1);
INSERT into [Pages] ([Id], [LinkSlug], [ParentPageId], [Order]) VALUES (6, 'page13', 1, 2);
INSERT into [Pages] ([Id], [LinkSlug], [ParentPageId], [Order]) VALUES (7, 'page21', 2, 0);
INSERT into [Pages] ([Id], [LinkSlug], [ParentPageId], [Order]) VALUES (8, 'page22', 2, 1);
INSERT into [Pages] ([Id], [LinkSlug], [ParentPageId], [Order]) VALUES (9, 'page121', 5, 0);
INSERT into [Pages] ([Id], [LinkSlug], [ParentPageId], [Order]) VALUES (10, 'page122', 5, 1);
INSERT into [Pages] ([Id], [LinkSlug], [ParentPageId], [Order]) VALUES (11, 'page123', 5, 2);

Upvotes: 2

Views: 309

Answers (2)

Idol
Idol

Reputation: 570

You can try this:

WITH cte_org (n, id, 
    LinkSlug,
    ParentPageId) AS (
SELECT
    CAST([order] as CHAR(200)),
    id, 
    LinkSlug,
    ParentPageId
FROM       
    pages
WHERE ParentPageId IS NULL
UNION ALL
SELECT 
    o.n || '_' || e.[order],
    e.id,
    e.LinkSlug,
    e.ParentPageId
FROM 
    pages e
    INNER JOIN cte_org o 
        ON o.id = e.ParentPageId)

SELECT * FROM cte_org order by n;

Note: that in MS SQL you need to use concat instead of ||; in MySQL - +

enter image description here

Upvotes: 1

John Cappelletti
John Cappelletti

Reputation: 81970

You would need a recursive CTE to build the hierarchy and maintain the sequence

Example

with cte1 as (
      Select [Id]
            ,[LinkSlug]
            ,[ParentPageId]
            ,[Order]
            ,Seq  = cast(10000+Row_Number() over (Order by [Order]) as varchar(500))
      From   pages
      Where  [ParentPageId] is null
      Union  All
      Select cte2.[Id]
            ,cte2.[LinkSlug]
            ,cte2.[ParentPageId]
            ,cte2.[Order]
            ,Seq  = cast(concat(cte1.Seq,'.',10000+Row_Number() over (Order by cte2.[Order])) as varchar(500))
      From   pages cte2 
      Join   cte1 on cte2.[ParentPageId] = cte1.[Id])
Select *
 From cte1
 Order By Seq

Results

enter image description here

Upvotes: 2

Related Questions