Re: Recursive CTE trees + Sorting by votes - Mailing list pgsql-general

On Wed, Aug 06, 2014 at 05:28:09PM -0400, Gregory Taylor wrote:
> We are working on a threaded comment system, and found this post by Disqus
> to be super helpful:
>
> http://cramer.io/2010/05/30/scaling-threaded-comments-on-django-at-disqus/
>
> The CTE works wonderfully, and we're really happy with the results. The
> last obstacle is figuring out how to sort by a "votes" field, meanwhile
> preserving the tree structure.

What do you mean exactly? Do you mean that want everything at the same
level to be sorted by vote?

> If we "ORDER BY path, votes" (assuming we have the same structure as in the
> article), we never need tie-breaking on "path", so the "votes" part of this
> doesn't even come into the equation.
>
> I suspect we need to do some path manipulation, but I'm not too sure of
> where to begin with this. I attempted incorporating "votes" into the path,
> but I failed pretty badly with this. It's probably way off, but here's my
> last (failed) attempt:
>
> https://gist.github.com/gtaylor/e3926a90fe108d52a4c8

I think what you need to do is do the ordering withing the CTE itself.
Something like:

WITH RECUSIVE cte () AS (
   SELECT ... ORDER BY vote DESC
UNION ALL
   SELECT ... JOIN cte ... ORDER BY vote DESC
) SELECT * from cte;

Or another idea, add a column that is the path of the parent:

WITH RECUSIVE cte () AS (
   SELECT array[] as path_parent, array[id] as path, ... ORDER BY vote DESC
UNION ALL
   SELECT cte.path as path_parent, cte.path || comments.id as path, ... JOIN cte ... ORDER BY vote DESC
) SELECT * from cte order by path, votes desc;