Re: For the SQL gurus out there

On Dec 13, 2007, at 6:55, Uwe C. Schroeder wrote:
> What I would like to do is to create a view that sucks the comments
> for a
> given blog_id in the order they should be displayed (very commonly
> seen in
> pretty much all blogging apps), i.e.
>
> Blog
> comment 1
>   comment on comment 1
>     comment on comment on comment 1
> comment 2
> etc.
>
>
> Question is, is there a smart way I'm not able to figure out to
> create a
> single query on the blog comment table that will return the
> comments in the
> right order? Sure I could write a recursive method that assembles
> the data in
> correct order, but I'd prefer to leave that to the database to
> handle in a
> view.
>
> The solution can be very postgresql specific, because I don't
> intend to run it
> on any other db server.

Have a look at the ltree contrib package. It allows you to specify
the comment hierarchy as a path and order by that, while look-ups on
it are still quite fast (no need to look up child records separately,
for example).

--
Alban Hertroys

        "It's not a bug!
         It's a six-legged feature!"



!DSPAM:737,47610cc19659110557619!