From: Cstdenis <lists@on-track.ca>
To: pgsql-sql@postgresql.org
Subject: Query to select nested comments sorted by nesting and date
Date: Mon, 20 Jun 2011 08:04:47 -0700
Message-ID: <4DFF618F.2020002@on-track.ca> (raw)
I am trying to select nested commentes from a table with this structure
CREATE TABLE picture_comments
(
comment_id serial NOT NULL,
user_id integer NOT NULL,
"comment" text NOT NULL DEFAULT ''::text,
comment_date timestamp without time zone NOT NULL DEFAULT now(),
ipaddr inet NOT NULL,
reply_to integer NOT NULL DEFAULT 0, -- ID of parent comment_id.
0 for comments that are not replies to other comments
deleted smallint NOT NULL DEFAULT 0,
id_tree ltree NOT NULL DEFAULT ''::ltree, -- ltree structure of
comment IDs 1.2.3.4, etc.
reply_date timestamp with time zone DEFAULT now(), --
comment_date of most recent reply (of any depth under it).
pid integer NOT NULL, -- Picture ID
}
The result needs to be sorted by date of most recent reply descending
(replying bumps the thread) but also need to be sorted such that the
parent/child relationships are maintained. Multiple replies on the same
level also need to be sorted by date desc.
Getting the parent/child sorting can be accomplished with a simple
"order by id_tree", but I can't find any way to combine that with date
sorting without breaking the nesting.
What is the most efficient way of making this work? I exparimented with
"WITH RECURSIVE" but it won't allow me to sort until the end so it
doesn't seem to help. Plus it appears to be much slower than just using
the ltree (100ms for ltree based vs 1.5 seconds for WITH RECURSIVE). I
could do the sorting in the php code, but it seems more efficient if I
can just do it all in a single SQL query.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: lists@on-track.ca
Subject: Re: Query to select nested comments sorted by nesting and date
In-Reply-To: <4DFF618F.2020002@on-track.ca>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox