Hi,
Steffan A. Cline wrote:
I am trying to set up a forum where there are main posts with replies and
replies to replies. Rather than using nested queries in my middleware I am
thinking there has to be some kind of recursive query where I can output the
results in a format like so:
MAIN
Reply to main
reply to reply to main
reply to main
reply to 2nd reply to main
MAIN
Reply
reply
....
The table structure is like so:
+-------------------+--------------+------+-----+-------------------+-------
---------+
| Field | Type | Null | Key | Default | Extra
|
+-------------------+--------------+------+-----+-------------------+-------
---------+
| forum_id | bigint(11) | NO | PRI | NULL |
auto_increment |
| project_id | bigint(11) | YES | MUL | 0 |
|
| forum_reply_id | bigint(11) | YES | MUL | 0 |
|
| forum_dev_id | bigint(11) | YES | MUL | 0 |
|
| forum_type | varchar(255) | YES | | |
|
| forum_subject | varchar(255) | YES | | |
|
| forum_message | longtext | YES | | NULL |
|
| forum_date_posted | timestamp | NO | | CURRENT_TIMESTAMP |
|
+-------------------+--------------+------+-----+-------------------+-------
---------+
Test data is like so
mysql> select * from forums;
+----------+------------+----------------+--------------+------------+------
---------+-------------------------------------------------+----------------
-----+
| forum_id | project_id | forum_reply_id | forum_dev_id | forum_type |
forum_subject | forum_message |
forum_date_posted |
+----------+------------+----------------+--------------+------------+------
---------+-------------------------------------------------+----------------
-----+
| 1 | 42 | 0 | 1 | | First
Post | I am the First! | 2007-07-08
15:09:41 |
| 2 | 42 | 1 | 1 | |
| I am a reply to the first | 2007-07-08 15:30:36 |
| 3 | 42 | 0 | 1 | |
sample data | this is some sample data in a new thread | 2007-07-08
15:10:03 |
| 4 | 42 | 2 | 1 | |
| this is a reply to the reply of the first post. | 2007-07-08 15:33:54 |
+----------+------------+----------------+--------------+------------+------
---------+-------------------------------------------------+----------------
-----+
4 rows in set (0.00 sec)
I am figuring that if the reply_to_id is 0 then it is a parent thread
otherwise it is a child or child of a child etc.
There are many ways to model hierarchies and trees in SQL. The one I think lends
itself best to this query is nested sets. Joe Celko's book is the classic on it,
though there are good descriptions of it in many places (Pro MySQL, and even for free
online, for example the sample chapters in http://www.artfulsoftware.com/). Celko and
others also give examples of other methods and the kinds of queries needed for them.
I'd have to check, but offhand I don't think there's any way to write the query you
want with the data structure you have, unless you use recursive Common Table
Expressions, which MySQL doesn't support.
Baron
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
