3

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

Improve this question
3
  • yeah some schema and sql info would help here. Commented Nov 30, 2009 at 21:35
  • LIMIT 2 would mean the query only returns 2 rows; I'm gathering you want to return a list that only goes 2 children deep? Commented Nov 30, 2009 at 21:35
  • Yes exactly... Ok to be more precise: Important Table struct: comment_id | parent_coment id | comment_date SQL query: SELECT SQL_CALC_FOUND_ROWS * FROM comments WHERE comment_post_id = '{$_REQUEST["ID"]}' ORDER BY comment_id, comment_date DESC LIMIT 10 Commented Nov 30, 2009 at 21:56

4 Answers 4

Reset to default
3

I was faced with this same problem, only I was going only one depth deep.

--Comment (depth: 0)
---Reply  (depth: 1)

I managed to use a single query to select all these records, while limiting the top level Comment records to only 10.

SELECT c.* FROM comments AS c WHERE c.OwnerId = 1 AND c.ParentId = 0 LIMIT 10
UNION
SELECT cc.* FROM comments AS cc
    INNER JOIN
    (
        SELECT CommentId FROM comments WHERE OwnerId = 1 AND ParentId = 0 LIMIT 10
    )
    AS c2
    ON cc.ParentId = c2.CommentId

This query basically does the following:

  • Gets the first 10 records that are top level comments, and have a specific owner id.
  • Gets all comments that have a parent id equal to the comment id that was returned by the first query, and unions them to the result set.

While, I think this query would be more efficient than making multiple calls to the database for each record, it still does have the flaw that it executes the first query twice. Once before the union, and one in the join on the union.

It does seem to be fairly quick, but not as quick as I would like. However, if your database is remote, and latency is an issue, this solution might serve you better than making multiple remote queries to the database.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Your example helped me with this very same problem. I had to change it although adding the condition to the UNION Select too, after closing INNER JOIN.
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.

Improve this answer

2 Comments

Hey thx for the answer.. but actually that's quite clear to me and i have a similar function already to format and order my comments... Maybe i am totally wrong but what i wanna avoid is fetching all the comments at once. But assure that all childs of a parent are fetched... I think a subquery could be more or less the solution... Something similar like get all parents, limit to 10 and fetch all their children...
Just need to dig a bit more in the mySQL documnetation... Maybe i'll find something
1

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.

Improve this answer

1 Comment

Uhmm, this is what i call an enlightment... Didnt know that i can join one and the same table to itself... WOW ;-) I do not know if this solves my limit problem but i'll give that a try ;) THANK YOU VERY MUCH!!!
0

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);
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.