From: | Gregory Taylor <gtaylor(at)gc-taylor(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Recursive CTE trees + Sorting by votes |
Date: | 2014-08-06 21:28:09 |
Message-ID: | CAA0B==T4boSEy06apYPqz5cyXsa8KzB173pOVutcSB5yJyOKdQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Martijn van Oosterhout | 2014-08-06 21:38:37 | Re: Recursive CTE trees + Sorting by votes |
Previous Message | Matthew Kelly | 2014-08-06 21:24:17 | The dangers of streaming across versions of glibc: A cautionary tale |