We are working on a threaded comment system, and found this post by Disqus to be super helpful:
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.
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:
Any ideas would be greatly appreciated! If we can retain the path structure and also sort by votes, we'll be able to paginate freely without issues.
--