Conditional limit in mySQL query possible?

Question

i am faced with a decicion regarding handling threaded comments in our project... I have a simple MySQL table which holds all the comments. There are two types: parents and childs. Childs represent a reply to a parent or another child.

My problem:

-Comment (depth 0)
-- Reply Child (depth 1)
--- Reply to previous child (depth 2)
-Comment (depth 0)

Imagine the above structure and a MySQL query with LIMIT 2. It would cut of the last reply (depth 2). Actually i would like to say something like: Try to limit to 2, if child left go on until the next parent. Tried several queries with no luck...

What i have right now is as followed:
SELECT SQL_CALC_FOUND_ROWS * FROM comments WHERE comment_post_id = '{$_REQUEST["ID"]}' ORDER BY comment_id, comment_date DESC LIMIT 10"

The important table fields are:
comment_id (index) | comment_parent_id (contains comment_id of parent or NULL)| comment_date

I would be very thankful for any ideas!!!

Saludos, Booosh

Answer 1

MySQL does not have any functions to parse tree-like structures. In the simplest scenario (child has an ID of the parent), you will need to programatically recurse into the tree to locate all the sub-nodes of a given node. MaxLevel indicates the depth you want to go to. It decrements with each recursive call so that at the end you end up with 0, which stops recursion.

e.g. (pseudo-code)

findNodes(String parentId, int maxLevel)
{
  select * from posts where parent = parentId
  foreach (result...)
  {
    if (maxLevel > 0) 
    {
      findNodes(result.nodeId, maxLevel - 1)
    }
    doSomethingWIthAResult
  }
}

To do this in a more concise way, there are a number of techniques, all of which involve some sort of index field that contains path to the current post. The path could look something like this: TopNode:Child1:Child2:Child3... In which you could do a select like this Select * from posts where path like "TopNode%" and depth = 2.