On Thu, Aug 7, 2014 at 11:57 AM, Paul Jungwirth <p...@illuminatedcomputing.com > wrote:
> > Or another idea, add a column that is the path of the parent: > > 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 > > This is outstanding, Paul. I'm still checking things over, but it looks like this is going to work. It looks like I was really close, but didn't think to go negative, and I had one of my arrays flip-flopped from what you've got. I made those two changes and it would appear that this is perfect. Much appreciated, I would have been beating my head against this for a lot longer without the help!