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!

Reply via email to