带有分数分解的评论线程

Question

我正在用头撞东西，我想知道是否有比我更熟练的人可以帮助我。

我的目标是创建一个评论线程，将评论评分系统考虑在内。

首先我会解释一下我现在在哪里。

假设我们有一篇文章的评论线程，如下例所示。括号中的数字是该评论的 ID。 ID 由数据库自动分配，并随着每条附加评论的发布而按时间顺序递增。评论文本前的破折号数量代表评论深度。

(01)"This is a top level comment." 
(02)-"This is a second level comment. A reply to the top level comment above."
(06)-"This is also a second level comment / another reply to comment 01."
(07)--"This is a reply to comment 06."
(03)"This is a different top level comment."
(05)-"This is a reply to the comment above."
(08)--"This is a reply to that comment in turn."
(10)---"This is a deeper comment still."
(04)"This is one more top level comment."
(09)-"This is one more reply."

我的第一个问题是以某种方式存储这些数据，这意味着它可以按正确的顺序返回。如果您只是存储深度字段并按深度排序，它将首先带回所有顶级注释，然后返回第二级注释等。这是不对的，我们必须返回完整出身完整的注释。

实现此目的的一种方法是存储每个评论的完整出身。

Comment ID  | Parentage
     01     |              (Comment 01 has no parent because it is top level)
     02     | 01-          (Comment 02 was a reply to comment 01)
     03     | 
     04     |              
     05     | 03-
     06     | 01-
     07     | 01-06-       (Comment 07 has two ancestors 01 and then 06)
     08     | 03-05-
     09     | 04-
     10     | 03-05-08-

添加另一个评论记录就像从您正在回复的评论中获取出身关系一样简单，然后附加其 ID 以形成新的出身关系。例如，如果我回复评论 10，我会获取其出身 (03-05-08-) 并附加其 ID (10-)。数据库会自动将其识别为第 11 条评论，我们会得到：

Comment ID  | Parentage
     01     | 
     02     | 01- 
     03     | 
     04     |              
     05     | 03-
     06     | 01-
     07     | 01-06-
     08     | 03-05-
     09     | 04-
     10     | 03-05-08-
     11     | 03-05-08-10-

现在，当我们订购要显示的评论时，我们对 Parentage 和 Comment ID 的串联进行排序，这给了我们：

Order by CONCAT(Parentage, ID)

Comment ID  | Parentage    |   CONCAT(Parentage, ID)
     01     |              |   01-
     02     | 01-          |   01-02-
     06     | 01-          |   01-06-
     07     | 01-06-       |   01-06-07-
     03     |              |   03-
     05     | 03-          |   03-05-
     08     | 03-05-       |   03-05-08-
     10     | 03-05-08-    |   03-05-08-10-
     11     | 03-05-08-10- |   03-05-08-10-11-
     04     |              |   04-
     09     | 04-          |   04-09-

这会产生与第一次演示完全相同的列表。我们后来添加的注释 11 插入到正确的位置：

(01)"This is a top level comment." 
(02)-"This is a reply to the top level comment."
(06)-"This is another reply that was posted later than the first."
(07)--"This is a reply to the second level comment directly above."
(03)"This is a different top level comment."
(05)-"This is a reply to the comment above."
(08)--"This is a reply to the comment above."
(10)---"This is a deeper comment still."
(11)----"THIS COMMENT WAS ADDED IN THE EARLIER EXAMPLE."
(04)"This is one more top level comment."
(09)-"This is one more reply."

可以通过检查 CONCAT 字符串的长度并将 len(CONCAT(Parentage, ID)) 乘以设定的像素数来完成缩进。这太棒了，我们有一个以识别其出身的方式存储评论的系统。

现在的问题是：

并非所有评论都是平等的。需要一个评论评分系统来区分好的评论。假设每条评论都有一个点赞按钮。虽然我们希望保留出身，但如果一条评论有两个同一级别的直接回复，那么我们希望首先显示点赞最多的评论。我将在下面的[方括号]中添加一些投票。

(01)"This is a top level comment." [6 votes]
(02)-"This is a reply to the top level comment." [2 votes]
(06)-"This is another reply that was posted later than the first." [30 votes]
(07)--"This is a reply to the second level comment directly above." [5 votes]
(03)"This is a different top level comment." [50 votes]
(05)-"This is a reply to the comment above." [4 votes]
(08)--"This is a reply to the comment above." [0 votes]
(10)---"This is a deeper comment still." [0 votes]
(11)----"THIS COMMENT WAS ADDED IN THE EARLIER EXAMPLE." [0 votes]
(04)"This is one more top level comment." [2 votes]
(09)-"This is one more reply." [0 votes]

在此示例中，评论 (01) 和 (03) 都是顶级评论，但 (03) 有 [50 票] 而 (01) 只有 [6 票]。 (01) 出现在上面只是因为它发布较早，因此被分配了一个较小的 ID。同样，(02) 和 (06) 都是对 (01) 的回复，但必须重新排序以使得票最多的 (06) 上升到顶部。

我完全、完全地致力于实现这一目标。

我想任何排序/重新排序和索引都最好在评论投票上完成，而不是在页面加载上完成，这样页面加载时间可以尽可能快，但除此之外我完全不知道！

您可以就可能的途径提出的任何想法或启示都会真正减轻负担！感谢您一如既往的帮助。

-------------------------------------------------- ------------------------------

编辑：响应@Paddy的解决方案，

当我在下面运行@Paddy提供的表达式时模拟数据时，我得到的第一个错误是：

"The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified." 

这可以通过将 SELECT 'top 100%' 添加到递归成员定义来解决。完成此操作后，我收到错误：

'CommentTree' has more columns than were specified in the column list.

这可以通过在 CommentTree 规范中添加“级别”列来解决。然后打印数据，但它首先返回所有顶级注释，然后返回类似于（但实际上不匹配）正确排序顺序的内容。

数据是这样返回的：

ParentId  |  CommentId  |  Comment  |  Vote  | Level
NULL      |      1      | Text here |   6    |  0
NULL      |      3      | Text here |   50   |  0     
NULL      |      4      | Text here |   2    |  0    
4         |      9      | Text here |   0    |  1    
3         |      5      | Text here |   4    |  1    
5         |      8      | Text here |   0    |  2    
8         |      10     | Text here |   0    |  3   
10        |      11     | Text here |   0    |  4    
1         |      2      | Text here |   2    |  1    
1         |      6      | Text here |   30   |  1     
6         |      7      | Text here |   5    |  2    

我做错了什么或者@Paddy 错过了什么吗？请接受我的歉意，递归函数对我来说很新。

Answer 1

下面的代码看起来很适合您的任务。它有点复杂，但在单个 SELECT 中完成它对我来说是一个挑战。您可以将其拆分为多个 SELECT 并预取到临时表中（出于性能目的），或者将其保留在一起。

谢谢你的问题，这很有趣！

请注意，根节点的 ParentID 必须为 0，而不是 NULL。

DECLARE @a TABLE (
    CommentID  INT,
    ParentID INT,
    Comment VARCHAR(100),
    Vote INT
)


INSERT @a
VALUES
    (1, 0, '', 6),
    (3, 0, '', 50),
    (4, 0, '', 2),
    (9, 4, '', 0),
    (5, 3, '', 4),
    (8, 5, '', 0),
    (10, 8, '', 0),
    (11, 10, '', 0),
    (2, 1, '', 2),
    (6, 1, '', 30),
    (7, 6, '', 5)

;WITH CTE_1 (ParentId, CommentId, Comment, Vote, Level, LevelPriority, Path)    -- prepare base info
AS
(
    SELECT c.ParentId, c.CommentId, c.Comment, c.Vote, 0 AS Level, ROW_NUMBER() OVER(ORDER BY c.Vote DESC), CAST('/' + CAST(c.CommentId AS VARCHAR(32)) AS VARCHAR(MAX)) + '/'
    FROM @a AS c
    WHERE ParentId = 0

    UNION ALL

    SELECT c.ParentId, c.CommentId, c.Comment, c.Vote, Level + 1 AS Level, ROW_NUMBER() OVER(ORDER BY c.Vote DESC), d.Path + CAST(c.CommentId AS VARCHAR(32)) + '/'
    FROM @a AS c
    INNER JOIN CTE_1 AS d
        ON c.ParentID = d.CommentID
),
CTE_2 (ParentId, CommentId, Comment, Vote, Level, LevelPriority, ChildCount)    -- count number of children
AS
(
    SELECT p.ParentId, p.CommentId, p.Comment, p.Vote, p.Level, p.LevelPriority, COUNT(*)
    FROM CTE_1 AS p
    INNER JOIN CTE_1 AS c
        ON c.Path LIKE p.Path + '%'
    GROUP BY 
        p.ParentId, p.CommentId, p.Comment, p.Vote, p.Level, p.LevelPriority
),
CTE_3 (ParentId, CommentId, Comment, Vote, Level, LevelPriority, OverAllPriority, ChildCount) -- calculate overall priorities
AS
(
    SELECT c.ParentId, c.CommentId, c.Comment, c.Vote, c.Level, c.LevelPriority, 1 AS OverAllPriority, ChildCount
    FROM CTE_2 AS c
    WHERE Level = 0 AND LevelPriority = 1

    UNION ALL

    SELECT c.ParentId, c.CommentId, c.Comment, c.Vote, c.Level, c.LevelPriority, 
        CASE 
            WHEN c.ParentID = d.CommentID THEN d.OverAllPriority + 1
            ELSE d.OverAllPriority + d.ChildCount
        END,
        c.ChildCount
    FROM CTE_2 AS c
    INNER JOIN CTE_3 AS d
        ON 
            (c.ParentID = d.CommentID AND c.LevelPriority = 1) 
            OR (c.ParentID = d.ParentID AND d.LevelPriority + 1 = c.LevelPriority)
)
SELECT ParentId, CommentId, Comment, Vote
FROM CTE_3
ORDER BY OverAllPriority

在此查询中，我执行以下操作：

1. 在 CTE_1 中，我计算同一父注释中的排序位置（基于投票）并构建树路径以收集有关层次结构中所有节点的信息。
2. 在 CTE_2 中，我计算属于每个节点的后代数量+1。树路径允许通过一个SELECT来计算所有级别的后代。
3. 在 CTE_3 中，我根据 3 个简单规则计算总体排序位置：
   1. 最上面一行的 position = 1
   2. 上层子节点的位置为 position =parent_position + 1
   3. 下一个同级应该位于前一个同级的所有后代之后，并且具有 position = prev_sibling_position + prev_sibling_number_of_descendants

编辑 相同的解决方案，但没有 CTE。

DECLARE @a TABLE (
    CommentID  INT,
    ParentID INT,
    Comment VARCHAR(100),
    Vote INT
)

INSERT @a
VALUES
    (1, 0, '', 6),
    (3, 0, '', 50),
    (4, 0, '', 2),
    (9, 4, '', 0),
    (5, 3, '', 4),
    (8, 5, '', 0),
    (10, 8, '', 0),
    (11, 10, '', 0),
    (2, 1, '', 2),
    (6, 1, '', 30),
    (7, 6, '', 5)


DECLARE @rows INT

DECLARE @temp_table TABLE (
    CommentID  INT,
    ParentID INT,
    Comment VARCHAR(100),
    Vote INT,
    LevelPriority INT, 
    Path VARCHAR(MAX),
    ChildCount INT NULL,
    OverAllPriority INT NULL
)

INSERT @temp_table (CommentID, ParentID, Comment, Vote, LevelPriority, Path)
SELECT CommentID, ParentID, Comment, Vote, ROW_NUMBER() OVER(ORDER BY Vote DESC), '/' + CAST(CommentId AS VARCHAR(32)) + '/'
FROM @a
WHERE ParentID = 0

SELECT @rows = @@ROWCOUNT

WHILE @rows > 0
BEGIN

    INSERT @temp_table (CommentID, ParentID, Comment, Vote, LevelPriority, Path)
    SELECT a.CommentID, a.ParentID, a.Comment, a.Vote, ROW_NUMBER() OVER(PARTITION BY a.ParentID ORDER BY a.Vote DESC), c.Path + CAST(a.CommentId AS VARCHAR(32)) + '/'
    FROM @a AS a
    INNER JOIN @temp_table AS c
        ON a.ParentID = c.CommentID
    WHERE NOT
        a.CommentID IN (SELECT CommentID FROM @temp_table)  

    SELECT @rows = @@ROWCOUNT
END

UPDATE c
SET ChildCount = a.cnt
FROM (
    SELECT p.CommentID, COUNT(*) AS cnt 
    FROM @temp_table AS p
    INNER JOIN @temp_table AS c
        ON c.Path LIKE p.Path + '%'
    GROUP BY 
        p.CommentID
) AS a
INNER JOIN @temp_table AS c
    ON a.CommentID = c.CommentID

UPDATE @temp_table
SET OverAllPriority = 1
WHERE ParentID = 0 AND LevelPriority = 1

SELECT @rows = @@ROWCOUNT

WHILE @rows > 0
BEGIN

    UPDATE c
    SET 
        OverAllPriority = CASE 
            WHEN c.ParentID = p.CommentID THEN p.OverAllPriority + 1
            ELSE p.OverAllPriority + p.ChildCount
        END
    FROM @temp_table AS p
    INNER JOIN @temp_table AS c
        ON (c.ParentID = p.CommentID AND c.LevelPriority = 1) 
            OR (p.ParentID = c.ParentID AND p.LevelPriority + 1 = c.LevelPriority)
    WHERE
        c.OverAllPriority IS NULL  
        AND p.OverAllPriority IS NOT NULL

    SELECT @rows = @@ROWCOUNT
END


SELECT * FROM @temp_table 
ORDER BY OverAllPriority

Answer 2

虽然与您的问题没有直接关系，但我的建议是更改为 嵌套集模型。我知道这需要大量返工，但迟早你会意识到这是最好的选择:)

Answer 3

使用类似这样的表定义（自引用键）：

Comment ID  |   Parent ID    |   Comment    |  Vote

然后您可以使用递归公用表表达式（这是在 MS SQL 中）来获取结果：

WITH CommentTree (ParentId, CommentId, Comment, Vote)
AS
(
-- Anchor member definition
    SELECT c.ParentId, c.CommentId, c.Comment, c.Vote,
        0 AS Level
    FROM dbo.Comments AS c
    WHERE ParentId IS NULL
    UNION ALL
-- Recursive member definition
    SELECT c.ParentId, c.CommentId, c.Comment, c.Vote,
        Level + 1 AS Level
    FROM dbo.Comments AS c
    INNER JOIN CommentTree AS d
        ON c.ParentID = d.CommentID
    Order by C.Vote
)
SELECT ParentId, CommentId, Comment, Vote FROM CommentTree

CTE 参考：

http://msdn.microsoft.com/en-us/library/ms186243.aspx