Message-ID: <46425E0C.8080804@cs.anu.edu.au>
Date: Thu, 10 May 2007 09:49:32 +1000
From: Robert Edwards <bob@cs.anu.edu.au>
User-Agent: Thunderbird 1.5.0.9 (X11/20070104)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
References: <20070509125520.GA17497@apartia.fr>
In-Reply-To: <20070509125520.GA17497@apartia.fr>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Subject: Re: query to select a linked list


Hi Louis-David,

I also have written a forum application using PostgreSQL.

My schema has a "threadid" for each posting, which is actually also the
"messageid" of the first posting in the thread, but that is irrelevant.

I can then just select all messages belonging to that thread. The actual
hierarchy of messages (which posting is in response to which) is dealt
with by a "parentid", identifying the messageid of the post being
responded to. Sorting that out is done by the middleware (PHP in this
case) - the SQL query simply returns all messages in the thread in a
single query. Because our database is somewhat busy, I have opted to
keep the queries to the database simple and let the middleware sort
out the heirarchical structure (which it is quite good at).

I hope this helps.

Bob Edwards.

Louis-David Mitterrand wrote:
> Hi,
> 
> To build a threaded forum application I came up the following schema:
> 
> forum
> ------
> id_forum | integer| not null  default nextval('forum_id_forum_seq'::regclass)
> id_parent| integer| 
> subject  | text   | not null
> message  | text   | 
> 
> Each message a unique id_forum and an id_parent pointing to the replied 
> post (empty if first post).
> 
> How can I build an elegant query to select all messages in a thread?
> 
> Thanks,
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly