Hello Martijn,

Thanks for the reply, my responses are inline below.
Each level of the tree should be sorted by vote, while retaining the
correct hierarchy. So the top level entry with the most votes should be at
the top, plus all of the items beneath it (with each level of the tree
under that row being sorted correctly).
It looks like you can't order within a CTE.
I got this recommendation from someone else, and think that it's probably
the way to go. I've been playing with it unsuccessfully so far, though.
Most certainly because I've got something weirded up. Here's what I have:


WITH RECURSIVE cte (
id, discussion_id, body, num_votes,
class_section_id, modified_time,
author_id, reply_parent_id,
path, votes_path, depth
) AS (
SELECT discussion_response.id, discussion_response.discussion_id,
discussion_response.body, discussion_response.num_votes,
discussion_response.last_edited_time,
discussion_response.class_section_id,
discussion_response.author_id,
discussion_response.reply_parent_id,
array[id] AS path,
array[num_votes, id] AS votes_path,
1 AS depth
FROM discussion_response
WHERE reply_parent_id IS NULL AND discussion_id=2763

UNION ALL

SELECT discussion_response.id, discussion_response.discussion_id,
discussion_response.body, discussion_response.num_votes,
discussion_response.last_edited_time,
discussion_response.class_section_id,
discussion_response.author_id,
discussion_response.reply_parent_id,
cte.path || discussion_response.id,
cte.votes_path || discussion_response.num_votes ||
discussion_response.id,
cte.depth + 1 AS depth
FROM discussion_response
JOIN cte ON discussion_response.reply_parent_id = cte.id
WHERE discussion_response.discussion_id=2763
)
SELECT * FROM cte ORDER BY votes_path DESC, path DESC LIMIT 50 OFFSET 0;

The problem with this is that non-root level (depth > 1) rows end up at the
top because of the ordering by votes_path. For example:

id=292839, num_votes=0, reply_parent_id=211957,
votes_path={2,211957,0,292839}, path={211957,292839}, depth=2
id=211957, num_votes=2, reply_parent_id=NULL, votes_path={2,211957},
path={211957}, depth=1

I understand why it is ordered this way, it's just not what I was hoping
for. Ideally this ends up like this:

id=211957, num_votes=2, reply_parent_id=NULL, votes_path={2,211957},
path={211957}, depth=1
id=292839, num_votes=0, reply_parent_id=211957,
votes_path={2,211957,0,292839}, path={211957,292839}, depth=2

Sorting by path causes the correct "tree" structure to be returned and in
the right order, but obviously it's not
sorted at all by votes.

I don't think this will work. The problem is you need the full path to
keep the children with their parents, but you also need the score. If
you make the path an array of (-votes, id) tuples (perhaps flattened
for simplicity), then you get the correct ordering. That way at every
stage you are sorting by votes, but still keeping children with their
parents:

comments=> WITH RECURSIVE cte (id, message, author, path, parent_id,
depth, votes) AS (
SELECT id,
message,
author,
array[-votes,id] AS path,
parent_id,
1 AS depth, votes
FROM comments
WHERE parent_id IS NULL
UNION ALL
SELECT comments.id,
comments.message,
comments.author,
cte.path || -comments.votes || comments.id,
comments.parent_id,
cte.depth + 1 AS depth, comments.votes
FROM comments
JOIN cte ON comments.parent_id = cte.id
)
SELECT id, message, author, path, depth, votes FROM cte
ORDER BY path;
id | message | author | path | depth | votes
----+-----------------------------+--------+-------------------+-------+-------
5 | Very interesting post! | thedz | {-3,5} | 1 | 3
8 | Fo sho, Yall | Mac | {-3,5,-12,8} | 2 | 12
7 | Agreed | G | {-3,5,-5,7} | 2 | 5
6 | You sir, are wrong | Chris | {-3,5,-3,6} | 2 | 3
1 | This thread is really cool! | David | {-1,1} | 1 | 1
3 | I agree David! | Daniel | {-1,1,-4,3} | 2 | 4
2 | Ya David, we love it! | Jason | {-1,1,-3,2} | 2 | 3
4 | gift Jason | Anton | {-1,1,-3,2,-15,4} | 3 | 15
(8 rows)

Time: 0.966 ms

Paul


On Wed, Aug 6, 2014 at 2:38 PM, Martijn van Oosterhout