使用分数因子分解的注释线程

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规范中添加“Level”列来解决。然后打印数据，但它首先返回所有顶级注释，然后返回与正确排序顺序相似(但实际上不匹配)的内容。

数据按如下方式返回：

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. In CTE_1 I计算同一父注释内的排序位置(基于投票)，并构建树路径以收集层次结构中所有节点的信息。
2. in CTE_2 I计算属于每个节点的子代数量+1。树路径允许以1为单位计算所有级别的子代CTE_3我基于3个简单规则计算整体排序位置：
   1. 最上面一行有上一个子节点，最后应该是下一个兄弟节点上一个的后代，并具有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

虽然与您的问题没有直接关系，但我的建议是改用Nested Set Model。我知道这是大量的返工，但迟早你会意识到这是最好的选择:)

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