Обсуждение: For the SQL gurus out there
Ok, something I've been toying around with for a while.
Here's the scenario:
Imagine a blogging app.
I have a table for blogs with a blog_id (primary key)
and a table blog_comments also with a comment_id as primary key and a foreign
key holding the blog_id the post belongs to.
The comments table also has a field that holds a self-reference to comment id
for commments on comments (on comments) of a blog.
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.
Any ideas anyone?
THX
UC
--
Open Source Solutions 4U, LLC 1618 Kelly St
Phone: +1 707 568 3056 Santa Rosa, CA 95401
Cell: +1 650 302 2405 United States
Fax: +1 707 568 6416
Uwe C. Schroeder wrote: > Ok, something I've been toying around with for a while. > Here's the scenario: > Imagine a blogging app. > I have a table for blogs with a blog_id (primary key) > and a table blog_comments also with a comment_id as primary key and a foreign > key holding the blog_id the post belongs to. > The comments table also has a field that holds a self-reference to comment id > for commments on comments (on comments) of a blog. > > 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. What you are looking for is the 'connectby' function found in contrib as part of the tablefunc package. On my CentOS 5.1 box, it's part of this RPM: ---------------------------------------- > rpm -qi postgresql-contrib Name : postgresql-contrib Relocations: (not relocatable) Version : 8.2.4 Vendor: (none) Release : 1PGDG Build Date: Fri 20 Apr 2007 01:58:54 PM CDT Install Date: Sun 16 Sep 2007 12:27:55 AM CDT Build Host: rhel5x8664.gunduz.org Group : Applications/Databases Source RPM: postgresql-8.2.4-1PGDG.src.rpm Size : 1724563 License: BSD Signature : DSA/SHA1, Fri 20 Apr 2007 02:14:40 PM CDT, Key ID a667b5d820579f11 URL : http://www.postgresql.org/ Summary : Contributed source and binaries distributed with PostgreSQL Description : The postgresql-contrib package contains contributed packages that are included in the PostgreSQL distribution. ---------------------------------------- And the files you want to look at is here: /usr/share/doc/postgresql-contrib-8.2.4/README.tablefunc It talks about connectby here: ---------------------------------------- ... Name connectby(text, text, text[, text], text, text, int[, text]) - returns a set representing a hierarchy (tree structure) Synopsis connectby(text relname, text keyid_fld, text parent_keyid_fld [, text orderby_fld], text start_with, int max_depth [, text branch_delim]) ... ---------------------------------------- Do some searching for 'connectby' and PostgreSQL in google. -- Dante
--- On Wed, 12/12/07, Uwe C. Schroeder <uwe@oss4u.com> wrote: > The comments table also has a field that holds a > self-reference to comment id > for commments on comments (on comments) of a blog. The model that you are referring to here is the adjacency list hierarchy model. There are two other models that will giveyou what you want but are harder to implement: path enumeration model and the nested set model. My guess is that thepath enumeration model with give you what to want with a decent insert performance. Aside from this there is the ltreecontribe module that take care of a lot of the work for you. > 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. Yup that is the problem with the adjacency list model. Regards, Richard Broersma Jr.
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!
Thanks everyone.
This was exactly what I needed. I went with connectby as Dante recommended
and it works like a charm.
UC
--
Open Source Solutions 4U, LLC 1618 Kelly St
Phone: +1 707 568 3056 Santa Rosa, CA 95401
Cell: +1 650 302 2405 United States
Fax: +1 707 568 6416