Conditional limit in mySQL query possible???

Question

Hi everyone, 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.

Answer 2

Always think about the question you really want to ask the database and then translate that into SQL - in this case you want "a list of all top-level comments with their immediate children, if any".

eg. (simplified)

SELECT * FROM comments c1
LEFT JOIN comments c2 ON c2.parent_comment_id=c1.comment_id
WHERE c1.parent_comment_id IS NULL
ORDER BY c1.comment_date, c1.comment_id, c2.comment_date, c2.comment_id;

With that result, you can write them out in the right order - if c2.comment_id is null, it's a top-level comment with no children, and if c1.comment_id is repeated, it's another child of the same comment.

Answer 3

I finally managed it based on Greg Adamskis hint... So dont vote my answer but check his one!!!

To describe the problem in short... We need a pagination for a comment list on our website. Using a standard limit could cause that some comments never would be shown... What we needed was a Limit which only affected our parent nodes not the cild nodes which are replies... Long story... however maybe one time this iis useful for someone:

    function getComments($comment_parent_id, $scope,&$comments, $db)
 {
  $res = $db->select("SELECT * FROM comments WHERE comment_post_id = '{$_REQUEST["ID"]}' AND comment_parent_id = '{$comment_parent_id}' ORDER BY comment_date DESC LIMIT {$scope}");

  while ($row = mysql_fetch_array($res, MYSQL_ASSOC)) 
  {
   $i = count($comments)+1;

   foreach ($row as $k => $v) {
       $comments[$i][$k] = $v;
   }

   //LOOK FOR REPLIES (childs of parent)
   if (mysql_num_rows($db->select("SELECT * FROM comments WHERE comment_parent_id = '{$row['comment_id']}' LIMIT 1")) != 0 ){
    getComments($row['comment_id'],100,$comments,$db);
   }
  }
 }

 //ARGUMENTS: parent_id (always starting with zero), scope, array holding comments, db class
 getComments(0,5,$comments,$db);

THANK YOU ALL!!!