Recursive CTE trees + Sorting by votes

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

Responses

Browse pgsql-general by date

  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