From: Robert Edwards <bob@cs.anu.edu.au>
To: pgsql-sql@postgresql.org
Subject: Re: query to select a linked list
Date: Thu, 10 May 2007 09:49:32 +1000
Message-ID: <46425E0C.8080804@cs.anu.edu.au> (raw)
In-Reply-To: <20070509125520.GA17497@apartia.fr>
References: <20070509125520.GA17497@apartia.fr>
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
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: bob@cs.anu.edu.au
Subject: Re: query to select a linked list
In-Reply-To: <46425E0C.8080804@cs.anu.edu.au>
* 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