I am trying to select nested commentes from a table with this structure<br /><blockquote>CREATE TABLE
picture_comments<br/> (<br /> comment_id serial NOT NULL,<br /> user_id integer NOT NULL,<br /> "comment" text
NOTNULL DEFAULT ''::text,<br /> comment_date timestamp without time zone NOT NULL DEFAULT now(),<br /> ipaddr inet
NOTNULL,<br /> reply_to integer NOT NULL DEFAULT 0, -- ID of parent comment_id. 0 for comments that are not replies
toother comments<br /> deleted smallint NOT NULL DEFAULT 0,<br /> id_tree ltree NOT NULL DEFAULT ''::ltree, --
ltreestructure of comment IDs 1.2.3.4, etc.<br /> reply_date timestamp with time zone DEFAULT now(), -- comment_date
ofmost recent reply (of any depth under it). <br /> pid integer NOT NULL, -- Picture ID<br /> }<br /></blockquote>
Theresult needs to be sorted by date of most recent reply descending (replying bumps the thread) but also need to be
sortedsuch that the parent/child relationships are maintained. Multiple replies on the same level also need to be
sortedby date desc.<br /><br /> Getting the parent/child sorting can be accomplished with a simple "order by id_tree",
butI can't find any way to combine that with date sorting without breaking the nesting.<br /><br /><br /> What is the
mostefficient way of making this work? I exparimented with "WITH RECURSIVE" but it won't allow me to sort until the end
soit doesn't seem to help. Plus it appears to be much slower than just using the ltree (100ms for ltree based vs 1.5
secondsfor WITH RECURSIVE). I could do the sorting in the php code, but it seems more efficient if I can just do it all
ina single SQL query. <br />