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; Hope this helps, -- Martijn van Oosterhout <klep...@svana.org> http://svana.org/kleptog/ > He who writes carelessly confesses thereby at the very outset that he does > not attach much importance to his own thoughts. -- Arthur Schopenhauer
signature.asc
Description: Digital signature
