如何保持孩子在父母之后出现的顺序

Question

按replyid 预期的顺序： 55, 57, 58, 59, 60, 56 -- 这样整个第一个父级回复及其所有子级都出现在第二个父级回复之前 以下 SQL 查询返回错误的结果顺序

WITH RECURSIVE t(replyid, replypid, depth, path, reply, replied, reply_userid) AS (
    (SELECT replyid, replypid, 0, array[replyid], reply, replied, replies.userid, u.displayname, u.email_address,
        (SELECT COUNT(*) FROM reply_revs WHERE replyid = replies.replyid) AS reply_revs
        FROM replies
        LEFT OUTER JOIN users u ON (replies.userid = u.userid)
        WHERE replypid is NULL AND postid = 31 ORDER BY replied)
    UNION ALL
    (SELECT r.replyid, r.replypid, t.depth+1, t.path || r.replypid, r.reply, r.replied, r.userid, u.displayname, u.email_address,
        (SELECT COUNT(*) FROM reply_revs WHERE replyid = r.replyid)
        FROM replies r
        JOIN t ON (r.replypid = t.replyid)
        LEFT OUTER JOIN users u ON (r.userid = u.userid)
        ORDER BY replied)
) SELECT * FROM t

replyid     replypid    depth   path            reply                               replied 
55      NULL        0       {55}        1st parent reply                    2011-02-13 11:40:48.072148-05
56      NULL        0       {56}        2nd parent reply                    2011-02-13 11:41:00.610033-05
57      55          1       {55,55}     1st child to 1st parent reply           2011-02-13 11:41:26.541024-05
58      55          1       {55,55}     2nd child to 1st parent reply           2011-02-13 11:41:39.485405-05
59      55          1       {55,55}     3rd child to 1st parent reply           2011-02-13 11:41:51.35482-05
60      59          2       {55,55,59}  1st child to 3rd child of 1st parent reply  2011-02-13 11:42:14.866852-05

但是，只需在末尾添加“ORDER BY path”即可修复此问题，但仅限于升序

WITH RECURSIVE t(replyid, replypid, depth, path, reply, replied, reply_userid) AS (
    (SELECT replyid, replypid, 0, array[replyid], reply, replied, replies.userid, u.displayname, u.email_address,
        (SELECT COUNT(*) FROM reply_revs WHERE replyid = replies.replyid) AS reply_revs
        FROM replies
        LEFT OUTER JOIN users u ON (replies.userid = u.userid)
        WHERE replypid is NULL AND postid = 31 ORDER BY replied)
    UNION ALL
    (SELECT r.replyid, r.replypid, t.depth+1, t.path || r.replypid, r.reply, r.replied, r.userid, u.displayname, u.email_address,
        (SELECT COUNT(*) FROM reply_revs WHERE replyid = r.replyid)
        FROM replies r
        JOIN t ON (r.replypid = t.replyid)
        LEFT OUTER JOIN users u ON (r.userid = u.userid)
        ORDER BY replied)
) SELECT * FROM t ORDER BY path

replyid replypid    depth   path            reply                               replied 
55      NULL    0       {55}        1st parent reply                    2011-02-13 11:40:48.072148-05
57      55      1       {55,55}     1st child to 1st parent reply           2011-02-13 11:41:26.541024-05
58      55      1       {55,55}     2nd child to 1st parent reply           2011-02-13 11:41:39.485405-05
59      55      1       {55,55}     3rd child to 1st parent reply           2011-02-13 11:41:51.35482-05
60      59      2       {55,55,59}  1st child to 3rd child of 1st parent reply  2011-02-13 11:42:14.866852-05
56      NULL    0       {56}        2nd parent reply                    2011-02-13 11:41:00.610033-05

因此，现在让我们尝试降序，改为附加“ORDER BY path DESC” 结果是：

replyid replypid    depth   path            reply                               replied 
56      NULL    0       {56}        2nd parent reply                    2011-02-13 11:41:00.610033-05
60      59      2       {55,55,59}  1st child to 3rd child of 1st parent reply  2011-02-13 11:42:14.866852-05
57      55      1       {55,55}     1st child to 1st parent reply           2011-02-13 11:41:26.541024-05
58      55      1       {55,55}     2nd child to 1st parent reply           2011-02-13 11:41:39.485405-05
59      55      1       {55,55}     3rd child to 1st parent reply           2011-02-13 11:41:51.35482-05
55      NULL    0       {55}        1st parent reply                    2011-02-13 11:40:48.072148-05

现在它看起来好像第一个父级回复的子级是第二个父级回复的子级。

我的问题是：我怎样才能对结果进行排序，以便子项或结果的深度 > > 0 总是出现在其相应的父项之后而不是其他父项之后？

我希望看到的结果：

replyid replypid    depth   path            reply                               replied 
56      NULL    0       {56}        2nd parent reply                    2011-02-13 11:41:00.610033-05
55      NULL    0       {55}        1st parent reply                    2011-02-13 11:40:48.072148-05
57      55      1       {55,55}     1st child to 1st parent reply           2011-02-13 11:41:26.541024-05
58      55      1       {55,55}     2nd child to 1st parent reply           2011-02-13 11:41:39.485405-05
59      55      1       {55,55}     3rd child to 1st parent reply           2011-02-13 11:41:51.35482-05
60      59      2       {55,55,59}  1st child to 3rd child of 1st parent reply  2011-02-13 11:42:14.866852-05

感谢 Freenode 上 #postgresql 中的 RhodiumToad，我能够提出以下 PHP 和 SQL 查询，效果非常好！

if (isset($_SESSION["userid"])) {
    $s_col1 = ", (SELECT COUNT(*) FROM votes WHERE replyid = replies.replyid AND userid = %d) AS reply_voted";
    $s_col2 = ", (SELECT COUNT(*) FROM votes WHERE replyid = r.replyid AND userid = %d)";
} else { $s_col1 = ""; $s_col2 = ""; }

if ($sort == "newest") { $s_arr1 = "-extract(epoch from replied)::integer"; $s_arr2 = " || -extract(epoch from r.replied)::integer"; }
else if ($sort == "oldest") { $s_arr1 = "extract(epoch from replied)::integer"; $s_arr2 = " || extract(epoch from r.replied)::integer"; }
else if ($sort == "topvotes") { $s_arr1 = "-votes"; $s_arr2 = " || -r.votes"; }
else { $s_arr1 = ""; $s_arr2 = ""; }

$sql = "WITH RECURSIVE t(replyid, replypid, depth, path, reply, replied, reply_userid) AS (
        (SELECT replyid, replypid, 0, array[$s_arr1,replyid], reply, replied, replies.userid, u.displayname, u.email_address,
            (SELECT COUNT(*) FROM reply_revs WHERE replyid = replies.replyid) AS reply_revs,
            (SELECT COUNT(*) FROM votes WHERE replyid = replies.replyid) AS reply_votes
            $s_col1
        FROM replies
        LEFT OUTER JOIN users u ON (replies.userid = u.userid)
        WHERE replypid is NULL AND postid = %d)
        UNION ALL
        (SELECT r.replyid, r.replypid, t.depth+1, t.path$s_arr2 || r.replyid, r.reply, r.replied, r.userid, u.displayname, u.email_address,
            (SELECT COUNT(*) FROM reply_revs WHERE replyid = r.replyid) AS reply_revs,
            (SELECT COUNT(*) FROM votes WHERE replyid = r.replyid) AS reply_votes
            $s_col2
        FROM replies r
        JOIN t ON (r.replypid = t.replyid)
        LEFT OUTER JOIN users u ON (r.userid = u.userid))
    ) SELECT * FROM t ORDER BY path";

Answer 1

在你的最后一个查询中，你确实有两种排序。父母可以按升序或降序排序，但孩子只能按升序排序。

看完这个之后，我相信你可以得到这样的解决方案。

   order by case 
        when depth = 0
            then path
    /*
      secret function that always returns the
      right numbers regardless of whether or not the sort is ascending.
    */
        else XXX_function('DESC', path)
    end desc;

我相信逻辑是合理的，但你必须弄清楚如何替换降序排列的数字，因为事情会“颠倒”。 （也许颠倒数组位置）

Answer 2

是什么让孩子成为第一个孩子？如果是回复日期，您也必须按此值排序。