Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id 89390B5DBFA
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Mon, 20 Jun 2011 12:14:47 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 45788-04 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Mon, 20 Jun 2011 15:14:41 +0000 (UTC)
X-Greylist: delayed 00:09:47.866719 by SQLgrey-1.7.6
Received: from fate.ctgameinfo.com (fate.ctgameinfo.com [50.22.51.114])
	by mail.postgresql.org (Postfix) with ESMTP id 4103DB5DBC7
	for <pgsql-sql@postgresql.org>; Mon, 20 Jun 2011 12:14:40 -0300 (ADT)
Received: from [192.168.1.100] (S01060016b606ed02.vc.shawcable.net
	[24.87.51.64])
	(using TLSv1 with cipher DHE-RSA-CAMELLIA256-SHA (256/256 bits))
	(No client certificate requested)
	by fate.ctgameinfo.com (Postfix) with ESMTPSA id 352554504F
	for <pgsql-sql@postgresql.org>; Mon, 20 Jun 2011 08:04:50 -0700 (PDT)
X-Virus-Status: Clean
X-Virus-Scanned: clamav-milter 0.97 at spamass
Message-ID: <4DFF618F.2020002@on-track.ca>
Date: Mon, 20 Jun 2011 08:04:47 -0700
From: Cstdenis <lists@on-track.ca>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US;
	rv:1.9.2.17) Gecko/20110414 Lightning/1.0b2 Thunderbird/3.1.10
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Query to select nested comments sorted by nesting and date
Content-Type: multipart/alternative;
	boundary="------------070303070506080509050104"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.899 tagged_above=-5 required=5 tests=BAYES_00=-1.9,
	HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 201106/70
X-Sequence-Number: 35536

This is a multi-part message in MIME format.
--------------070303070506080509050104
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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.

--------------070303070506080509050104
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>

    <meta http-equiv="content-type" content="text/html; charset=ISO-8859-1">
  </head>
  <body bgcolor="#ffffff" text="#000000">
    I am trying to select nested commentes from a table with this
    structure<br>
    <blockquote>CREATE TABLE picture_comments<br>
      (<br>
      &nbsp; comment_id serial NOT NULL,<br>
      &nbsp; user_id integer NOT NULL,<br>
      &nbsp; "comment" text NOT NULL DEFAULT ''::text,<br>
      &nbsp; comment_date timestamp without time zone NOT NULL DEFAULT now(),<br>
      &nbsp; ipaddr inet NOT NULL,<br>
      &nbsp; reply_to integer NOT NULL DEFAULT 0, -- ID of parent comment_id.
      0 for comments that are not replies to other comments<br>
      &nbsp; deleted smallint NOT NULL DEFAULT 0,<br>
      &nbsp; id_tree ltree NOT NULL DEFAULT ''::ltree, -- ltree structure of
      comment IDs 1.2.3.4, etc.<br>
      &nbsp; reply_date timestamp with time zone DEFAULT now(), --
      comment_date of most recent reply (of any depth under it). <br>
      &nbsp; pid integer NOT NULL, -- Picture ID<br>
      }<br>
    </blockquote>
    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.<br>
    <br>
    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.<br>
    <br>
    <br>
    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. <br>
  </body>
</html>

--------------070303070506080509050104--