Order comments by thread path and by number of total votes

Question

I'm having some trouble ordering comments by their thread path and by number of upvotes of each comment.

Now they are only ordering by thread path. I've tried and searched a lot of things but nothings results.

This is my query

WITH RECURSIVE first_comments AS (
(
 (
   SELECT id, text, level, parent_id, array[id] AS thread_path, total_votes FROM comments
   WHERE comments."postId" = 1 AND comments."level" = 0 
 )
)
UNION
 (
  SELECT e.id, e.text, e.level, e.parent_id, (fle.thread_path || e.id), e.total_votes
  FROM
  (
    SELECT id, text, level, parent_id, total_votes FROM comments
    WHERE comments."postId" = 1
  ) e, first_comments fle
  WHERE e.parent_id = fle.id
 )
)
SELECT id, text, level, total_votes, thread_path from first_comments ORDER BY 5 ASC

This query results in:

--------------------------------------------------
| id  | level  | total_votes |    thread_path    |
--------------------------------------------------
| 1   |   0    |      5      |  {1}              |
| 3   |   1    |      9      |  {1,3}            |
| 7   |   2    |      5      |  {1,3,7}          |
| 9   |   2    |      7      |  {1,3,9}          |
| 11  |   3    |      0      |  {1,3,9,11}       |
| 12  |   4    |      0      |  {1,3,9,11,12}    |
| 13  |   5    |      0      |  {1,3,9,11,12,13} |
| 10  |   1    |     20      |  {1,10}           |
| 2   |   0    |     10      |  {2}              |
| 6   |   1    |      1      |  {2,6}            |
| 4   |   0    |      8      |  {4}              |
| 8   |   1    |      6      |  {4,8}            |
| 5   |   0    |      3      |  {5}              |
--------------------------------------------------

And the result should be

--------------------------------------------------
| id  | level  | total_votes |    thread_path    |
--------------------------------------------------
| 2   |   0    |     10      |  {2}              |
| 6   |   1    |      1      |  {2,6}            |
| 4   |   0    |      8      |  {4}              |
| 8   |   1    |      6      |  {4,8}            |
| 1   |   0    |      5      |  {1}              |
| 10  |   1    |     20      |  {1,10}           |              
| 3   |   1    |      9      |  {1,3}            |
| 9   |   2    |      7      |  {1,3,9}          |
| 11  |   3    |      0      |  {1,3,9,11}       |
| 12  |   4    |      0      |  {1,3,9,11,12}    |
| 13  |   5    |      0      |  {1,3,9,11,12,13} |
| 7   |   2    |      5      |  {1,3,7}          |
| 5   |   0    |      3      |  {5}              |
--------------------------------------------------

What I'm missing here...?

Thank for the help

Answer 1

Just accumulate another array next to path, witch will contain not just the id of each comment in its path, but the total_votes (as a negative number) before each id. After that, you can order by that column.

WITH RECURSIVE first_comments AS (
(
 (
   SELECT id, text, level, parent_id, array[id] AS path, total_votes,
          array[-total_votes, id] AS path_and_votes
   FROM comments
   WHERE comments."postId" = 1 AND comments."level" = 0 
 )
)
UNION
 (
  SELECT e.id, e.text, e.level, e.parent_id, (fle.path || e.id), e.total_votes,
         (fle.path_and_votes || -e.total_votes || e.id)
  FROM
  (
    SELECT id, text, level, parent_id, total_votes FROM comments
    WHERE comments."postId" = 1
  ) e, first_comments fle
  WHERE e.parent_id = fle.id
 )
)
SELECT id, text, level, total_votes, path from first_comments ORDER BY path_and_votes ASC

SQLFiddle (only data -- without the recursive CTE)

Answer 2

You want to order by the total votes at the top level. I think I'll approach this by by using a window function.

Instead of:

SELECT id, text, level, total_votes, path
from first_comments
ORDER BY 5 ASC;

which explicitly orders by the path. Try this:

select id, text, level, total_votes,
       max(total_votes) over (partition by path[1]) as toplevel_votes
from first_comments
order by 6 desc;

This calculates the total votes at the top most level and uses that for ordering.