How to select a hierarchical post list

[pittendrigh]

(I originally put this post in the PHP forum. But here in databases is a better fit. Perhaps an admin will delete the PHP post.)

I want a (php-based) forum I can install as the contents of a DIV, rather than as a independent page (no HTML, HEAD or BODY elements). There are a few simple forums out there that could be hacked into submission, but none that support a "hierarchical, threaded message" view, which I also want.

So I'm thinking about writing my own bare bones but threaded forum (similar to the way usenet displays).
Perhaps this could be done with three tables: user, forum and post,
where the post.parent_post_id would be zero for thread heads.
post.indent_count would be one more than the indent_count for the parent_post_id.
The initial forum display would show thread head subjects only, where post.post_parent_id = 0.
But if any such thread head link was selected an indented/threaded list of child posts would be be displayed too.

Ordering the display, so all the first level children of post_id = X would appear both indented and below the parent subject line could be ddone with an enormously expensive recursive procedure. But I doubt that's a good idea.

Perhaps there is a way to use a table similar to what is below, combined with "order by" and "group by" ....perhaps using a cursor or two, to pull out the posts in the requisite order, and where post.indent_count kludges the hierarchical display
...or am I barking up an impossible tree?

CREATE TABLE post (
post_id int(4) NOT NULL auto_increment,
forum_id int(4) NOT NULL references forum(fid),
user_id int(4) NOT NULL references thread_user(uid),
parent_post_id int(4) NOT NULL default 0 references post(post_id),
indent_count int(4) NOT NULL default 0,
title varchar(96) NOT NULL,
body text,
PRIMARY KEY (post_id)
) ENGINE=MyISAM

...what would the select be?

[r937]

was there a database question in there somewhere?

[pittendrigh]

What would be the select statement?

[r937]

the select statement for what?

[pittendrigh]

Well. I doubt it's possible. But it would be powerful if it was...

To retrieve posts (from the example table at thread top) in a complex ordering, where each post would be immediately followed by its first level child responses, and (recursively) where each first level child post would be followed by a list of its first level child responses. This could be done with a separate recursive select for each post in a hierarchy. But that would be too expensive in a busy forum.

Could this be the output of a single select?
If the ordering were correct, and because each such post would have a post.indent_count, a usenet-like hierarchical display could be made.......hopefully form a single select statement.

[r937]

if you want to eliminate looping or recursion, you could use celko's nested set model

with your adjacency model (characterized by the parent_id foreign key), you can also set up a single query with as many joins as there are levels in the hierarchy

see Categories and Subcategories

[pittendrigh]

Thank you. That helps.
(Celko etc...I do have his Trees and Hierarchies book)

I did look at the source code for the phorum forum (which does support threaded displays).
They use a relatively simple select to populate a hashed hash. And then use post processing sort routines (in PHP) to rearrange that list, prior to constructing the visual display.

Threaded forums are tricky. That's probably why so few of them support it.

======another tack========
Posts could be separated out into two tables: thread_head and thread_response.
The thread_head table could hold an XML blob that would get updated for each response added to that thread. The XML would maintain the parent child relationships (sequence and indenting data). Mysql now supports XPATH queries. So the sequencing and indenting of the hierarchical display (and link construction) could come from the XML. Clicking any such individual link would invoke a subsequent sql select to get author, subject, post date and response text.

[Max Height]

Personally I would use the nested set model because I find it easier. But both the nested set and adjacency list models have their pros and cons.

With the nested set model you can have unlimited categories and sub categories and you are simply using "left" and "right" values to denote the position of a node (category of some sort) in the hierarchy. To add/delete/move nodes (and any of their children) all you essentially do is manipulate the left and right values of the nodes.