List:General Discussion« Previous MessageNext Message »
From:Baron Schwartz Date:July 9 2007 12:07pm
Subject:Re: Recursive queries
View as plain text  
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
Thread
Recursive queriesSteffan A. Cline9 Jul
  • Re: Recursive queriesBaron Schwartz9 Jul
  • Re: Recursive queriesDavid T. Ashley9 Jul