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. 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 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. -- Greg Taylor http://gc-taylor.com
