Re: Recursive CTE trees + Sorting by votes

On Thu, Aug 7, 2014 at 11:57 AM, Paul Jungwirth <pj(at)illuminatedcomputing(dot)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!