postgresql - 如何使用公用表表达式创建嵌套评论和回复树

Question

我以为我已经解决了这个问题，但是我没有。

我希望让 PostgreSQL 以一种可以在客户端呈现而无需额外排序的顺序输出评论和回复。

我有以下公用表表达式，我已经尝试了很多，但我还没有得到我需要的输出。

WITH RECURSIVE comment_tree AS (
SELECT 
    id           AS cte_id, 
    reply_to     AS cte_reply_to, 
    "createdAt"  AS cte_date, 
    body         AS cte_body, 
    commenter_id AS cte_commenter_id, 
    1            AS level, 
    ARRAY[0]     AS sort, 
    article_id 
FROM "Comments" 
WHERE reply_to IS NULL 
UNION ALL 
SELECT 
    c.id, 
    c.reply_to, 
    c."createdAt", 
    c.body, 
    c.commenter_id, 
    p.level + 1     AS level, 
    p.sort || p.level, 
    c.article_id 
FROM "Comments" c 
JOIN comment_tree p ON c.reply_to = p.cte_id) 
SELECT 
    sort,
    cte_body         AS body, 
    cte_id           AS id, 
    cte_reply_to     AS reply_to, 
    cte_date         AS "createdAt", 
    cte_commenter_id AS commenter_id, 
    level 
FROM comment_tree 
ORDER BY sort

结果如下。

我需要的是

如何获得排序以重现手动创建的正文列？

Answer 1

这是我最终使用的解决方案。从数据库中返回所有项目后，我检查了每个顶级评论的 child ，然后递归地检查他们的 child ，直到没有找到。

function(data){

    // Initialise 
    var comments = data[0];
    var topLevelComments = [];
    var commentsByReplyToValue = {}; 
    var commentsSorted = [];

    // Split 
    for(var i = comments.length -1; i >= 0; i--){ 
        if(comments[i].reply_to == null){ 
            topLevelComments.push(comments.splice(i,1)[0]); 
        }
    }

    for(var j = comments.length -1; j >= 0; j--){ 
        var currentReplyTo = comments[j].reply_to;

        if(commentsByReplyToValue.hasOwnProperty(currentReplyTo)){ 
            commentsByReplyToValue[currentReplyTo].push(comments.splice(i,1)[0]);
        }else{ 
            commentsByReplyToValue[currentReplyTo] = [];
            commentsByReplyToValue[currentReplyTo].push(comments.splice(i,1)[0]);
        }

    }

    // Sort Top Levels 
    sortByDateAsc(topLevelComments);


    // Sort Lower Levels
    var replyToKey, obj, prop, owns = Object.prototype.hasOwnProperty;
    for (replyToKey in commentsByReplyToValue ) {
        if (owns.call(commentsByReplyToValue, replyToKey)) {
            sortByDateDesc(commentsByReplyToValue[replyToKey]);
        }
    }

    //Join together 
    findChildren(topLevelComments, commentsByReplyToValue, commentsSorted); 


    return commentsSorted;
}