Как сохранить порядок появления детей после родителей

Question

Как сохранить порядок появления детей после родителей

Ожидаемый порядок по replyid: 55, 57, 58, 59, 60, 56 - чтобы весь 1-й родительский ответ и все его дочерние элементы появлялись ДО 2-го родительского ответа Следующий 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

Итак, давайте попробуем DESCENDING сейчас, вместо этого добавив " 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

Благодаря RhodiumToad в #postgresql на Freenode я смог придумать следующий запрос 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";

0

postgresql recursion recursive-query threaded-comments

Источник

user565778 14 фев '11 в 16:43

2 ответа

Другие вопросы по тегам postgresql recursion recursive-query threaded-comments

user397474 14 фев '11 в 23:02 2011-02-14 23:02 · Answer 1 · 2011-02-14 23:02

У вас действительно есть два вида в одном на ваш последний запрос. Родители могут сортировать по возрастанию или по убыванию, а дети могут сортировать только по возрастанию.

Посмотрев на это, я верю, что вы можете получить решение с чем-то вроде этого.

   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;

Я считаю, что логика логична, но вы должны выяснить, как заменить числа в порядке убывания, поскольку все будет "вверх ногами". (Возможно, поменять позиции массива)

user271959 14 фев '11 в 19:36 2011-02-14 19:36 · Answer 2 · 2011-02-14 19:36

Что делает ребенка первым ребенком? Если это ответная дата, вы должны также упорядочить по этому значению.

0

Источник

user271959 14 фев '11 в 19:36