使用递归 SQL 创建 DNN 导航菜单

Question

我正在开发一个包含树形导航菜单的 DotNetNuke 模块。

到目前为止，我已经可以正常工作了，从某种意义上说，子节点已连接到其正确的父节点，但节点兄弟姐妹仍然没有顺序。有一个名为 TabOrder 的字段，用于确定兄弟姐妹的顺序，但由于递归，我无法对它们进行正确排序。

我正在尝试在 SQL Server 存储过程中执行此操作，这可能是一个错误，但我觉得我已经很接近了，必须有一个解决方案。有谁知道我做错了什么？

如果您有任何想法，我将不胜感激。提前致谢。

---

解决方案：

我终于找到了我的问题的解决方案。关键是递归地创建从根选项卡到叶选项卡的选项卡沿袭（TabLevel + TabOrder）。创建后，我就能够正确排序返回的记录。

然而，当我回来发布这篇文章时，我看到了 MarkXA 的答案，这可能是最好的解决方案。我什至不知道 GetNavigationNodes 方法的存在。

我认为他是正确的，使用 GetNavigationNodes 是一种更面向未来的解决方案，但目前我将使用基于 SQL 的解决方案。 ——我能说什么？我学习的方式很艰难。

这里是：

ALTER procedure [dbo].[Nav_GetTabs]
    @CurrentTabID   int = 0
AS

--============================================================
--create and populate @TabLineage table variable with Tab Lineage
--
--"Lineage" consists of the concatenation of TabLevel & TabOrder, concatenated recursively from the root to leaf.
--The lineage is VERY important, making it possible to properly order the Tab links in the navigation module.
--This will be used as a lookup table to match Tabs with their lineage.
--============================================================
DECLARE @TabLineage table
    (
        TabID       int,
        Lineage     varchar(100)
    );

WITH TabLineage AS
(
    --start with root Tabs
    SELECT T.TabID, T.ParentID, CAST(REPLICATE('0', 5 - LEN(CAST(T2.[Level] as varchar(10)) + CAST(T2.TabOrder as varchar(10)))) + CAST(T2.[Level] as varchar(10)) + CAST(T2.TabOrder as varchar(10)) as varchar(100)) AS Lineage
        FROM Tabs T 
            INNER JOIN Tabs T2 ON T.TabID = T2.TabID
            INNER JOIN TabPermission TP ON T.TabID = TP.TabID
        WHERE T.ParentID IS NULL
            AND T.IsDeleted = 0 
            AND T.IsVisible = 1 
            AND TP.RoleID = -1

    UNION ALL

    --continue recursively, from parent to child Tabs
    SELECT T.TabID, T.ParentID, CAST(TL.Lineage + REPLICATE('0', 5 - LEN(CAST(T2.[Level] as varchar(10)) + CAST(T2.TabOrder as varchar(10)))) + CAST(T2.[Level] as varchar(10)) + CAST(T2.TabOrder as varchar(10)) as varchar(100)) AS Lineage
        FROM Tabs T
            INNER JOIN Tabs T2 ON T.TabID = T2.TabID
            INNER JOIN TabPermission TP ON T.TabID = TP.TabID
            INNER JOIN TabLineage TL ON T.ParentID = TL.TabID
        WHERE T.IsDeleted = 0 
            AND T.IsVisible = 1 
            AND TP.RoleID = -1
)
--insert results of recursive query into temporary table
INSERT @TabLineage
    SELECT TL.TabID, TL.Lineage FROM TabLineage TL ORDER BY TL.Lineage
    OPTION (maxrecursion 10);   --to increase number of traversed generations, increase "maxrecursion"


--============================================================
--create and populate @Ancestor table variable with @CurrentTab ancestors
--
--"Ancestors" are Tabs following the path from @CurrentTab to the root Tab it's descended from (inclusively).
--These are Tab links we want to see in the navigation.
--============================================================
DECLARE @Ancestor   table
    (
        TabID       int
    );

WITH Ancestor AS
(
    --start with @CurrentTab
    SELECT T.TabID, T.ParentID FROM Tabs T WHERE T.TabID = @CurrentTabID

    UNION ALL

    --continue recursively, from child to parent Tab
    SELECT T.TabID, T.ParentID
        FROM Ancestor A INNER JOIN Tabs T ON T.TabID = A.ParentID
)

--insert results of recursive query into temporary table
INSERT @Ancestor
    SELECT A.TabID FROM Ancestor A
    OPTION (maxrecursion 10);   --to increase number of traversed generations, increase "maxrecursion"


--============================================================
--retrieve Tabs to display in navigation

--This section UNIONs three query results together, giving us what we want:
-- 1. All Tabs at Level 0.
-- 2. All Tabs in @CurrentTab's lineage.
-- 3. All Tabs which are children of Tabs in @CurrentTab's lineage.
--============================================================
WITH TabNav (TabID, TabLevel, TabName, Lineage) AS
(
    --retrieve all Tabs at Level 0 -- (Root Tabs)
    (SELECT T.TabID, T.[Level] AS TabLevel, T.TabName, TL.Lineage
    FROM Tabs T 
        INNER JOIN TabPermission TP ON (T.TabID = TP.TabID AND TP.RoleID = -1)
        INNER JOIN @TabLineage TL ON T.TabID = TL.TabID
    WHERE T.IsDeleted = 0 
        AND T.IsVisible = 1 
        AND T.[Level] = 0

    UNION

    --retrieve Tabs in @CurrentTab's lineage
    SELECT T.TabID, T.[Level] AS TabLevel, T.TabName, TL.Lineage
    FROM Tabs T 
        INNER JOIN TabPermission TP ON (T.TabID = TP.TabID AND TP.RoleID = -1)
        INNER JOIN @Ancestor A ON T.TabID = A.TabID
        INNER JOIN @TabLineage TL ON T.TabID = TL.TabID
    WHERE T.IsDeleted = 0 
        AND T.IsVisible = 1 

    UNION

    --retrieve Tabs which are children of Tabs in @CurrentTab's lineage
    SELECT T.TabID, T.[Level] AS TabLevel, T.TabName, TL.Lineage
    FROM Tabs T 
        INNER JOIN TabPermission TP ON (T.TabID = TP.TabID AND TP.RoleID = -1)
        INNER JOIN @Ancestor A ON T.ParentID = A.TabID
        INNER JOIN @TabLineage TL ON T.TabID = TL.TabID
    WHERE T.IsDeleted = 0 
        AND T.IsVisible = 1)
)

--finally, return the Tabs to be included in the navigation module
SELECT TabID, TabLevel, TabName FROM TabNav ORDER BY Lineage;
--============================================================

Answer 1

答案是“不要使用 SQL”。已经有一个方法 DotNetNuke.UI.Navigation.GetNavigationNodes 可以为您执行此操作，如果您使用它，那么即使数据库架构发生更改，您的模块也不会中断。即使您需要做一些 GetNavigationNodes 无法处理的事情，您仍然最好通过 API 检索页面，以便面向未来。直接去数据库只是自找麻烦:)

Answer 2

这里是一个递归树 CTE 的样板（不基于给定 OP 的代码）示例，它展示了如何对树进行排序：

DECLARE @Contacts table (id varchar(6), first_name varchar(10), reports_to_id varchar(6))
INSERT @Contacts VALUES ('1','Jerome', NULL )  -- tree is as follows:
INSERT @Contacts VALUES ('2','Joe'   ,'1')     --                      1-Jerome
INSERT @Contacts VALUES ('3','Paul'  ,'2')     --                     /        \
INSERT @Contacts VALUES ('4','Jack'  ,'3')     --              2-Joe           9-Bill
INSERT @Contacts VALUES ('5','Daniel','3')     --            /       \              \
INSERT @Contacts VALUES ('6','David' ,'2')     --     3-Paul          6-David       10-Sam
INSERT @Contacts VALUES ('7','Ian'   ,'6')     --    /      \            /    \
INSERT @Contacts VALUES ('8','Helen' ,'6')     -- 4-Jack  5-Daniel   7-Ian    8-Helen
INSERT @Contacts VALUES ('9','Bill ' ,'1')     --
INSERT @Contacts VALUES ('10','Sam'  ,'9')     --

DECLARE @Root_id  varchar(6)

--get all nodes 2 and below
SET @Root_id=2
PRINT '@Root_id='+COALESCE(''''+@Root_id+'''','null')
;WITH StaffTree AS
(
    SELECT 
        c.id, c.first_name, c.reports_to_id, c.reports_to_id as Manager_id, cc.first_name AS Manager_first_name, 1 AS LevelOf
        FROM @Contacts                  c
            LEFT OUTER JOIN @Contacts  cc ON c.reports_to_id=cc.id
        WHERE c.id=@Root_id OR (@Root_id IS NULL AND c.reports_to_id IS NULL)
    UNION ALL
        SELECT 
            s.id, s.first_name, s.reports_to_id, t.id, t.first_name, t.LevelOf+1
        FROM StaffTree            t
            INNER JOIN @Contacts  s ON t.id=s.reports_to_id
    WHERE s.reports_to_id=@Root_id OR @Root_id IS NULL OR t.LevelOf>1
)
SELECT * FROM StaffTree ORDER BY LevelOf, first_name

输出：

@Root_id='2'
id     first_name reports_to_id Manager_id Manager_first_name LevelOf
------ ---------- ------------- ---------- ------------------ -----------
2      Joe        1             1          Jerome             1
6      David      2             2          Joe                2
3      Paul       2             2          Joe                2
5      Daniel     3             3          Paul               3
8      Helen      6             6          David              3
7      Ian        6             6          David              3
4      Jack       3             3          Paul               3

(7 row(s) affected)

关键是 LevelOf 列。在 CTE 中选择主要父级时，看看它如何只是一个文字 1。然后，LevelOf 列在递归 CTE 的 UNION ALL 部分中递增。对 CTE 的每次递归调用（不是行）都会命中该 UNION ALL 一次并递增。没有比这更多的了。