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