When pulling comments from a database table, it can be very costly when you are performing a query that will automatically sort all of the comments into the parent-child hierarchy. Every extra ms your SQL server spends performing calculations is time that is wasted. If you can easily move this logic to the programming language that you are using it will free up your DB server to serve more data. If you have a blog post that has comments, obtaining the comments from the DB for this post is a simple process, but the problem is that the order of the comments will not be right. If someone replies to the first comment days after lots of other comments have been made, this comment will show up at the end of the list returned from the DB. There are ways in which you can construct your query so that it will return an array of child comments for each comment, but this is way too wasteful. Using a simple recursive method you can sort out the comments using a programming language so as not to lock up your DB server any longer.
A simple SQL query like this is going to pull some comments from a database table that is storing comments for a blog post.
SELECT * FROM Comments WHERE postid=123
This query now leaves you with a list of comments that are a mix of parent and child comments are not in order. This seems a bit messy and can be a daunting problem to solve, but look at how simple that SQL query is. It is worth the effort when your DB server has very little to do to sort the comments. We are going to start this by creating the recursive function. A recursive function is a function that calls itself many times. The following function is written in PHP, but can be easily translated to any other programming languages.
function GetChildren($parentid, $comments)
{
$childcomments = array();
foreach($comments as $comment)
{
//if the comment has a prent id of 0 then it must be a parent. a parent comment cannot be a child. Also check to see the comment being checked isnt the comment we are getting children for otherwise we will get stuck in an infinite loop
if($comment['parentid'] != 0 && $comment['parentid'] == $parentid)
{
$comment["children"] = GetChildren($comment['id'], $comments);
$childcomments[] = $comment;
}
}
return $childcomments;
}
This function accepts 2 parameters. The parent ID which is the Id of the comment that we want to get the child comments for, and then it contains the full list of all comments that were returned from the database query. When you want to see if a comment has children, you will loop through all of the comments checking to see if any of the comments have a parent ID that is equal to the current comment. Once we find a match we first need to check and make sure that this child comment doesn’t also have child comments,. This is why we use a recursive method. We cant tell how many comments have replies and how deep they go. This way we can do it without needing to know. Once we have checked for more child comments we can add this child comment to an array of child comments for the current comment we are checking.
The only thing left to do now is to determine how to set it off. We need a way to call this method and restart the recursive loop. Here is a completed method that will show you how to pull them from the database and then how to trigger the calling of the recursive loop.
$stmt = $db->prepare("SELECT * FROM Comments WHERE postid=?");
$stmt->execute(array("123"));
$comments = $stmt->fetchAll();
$sortedcomments = array();
foreach($comments as $comment)
{
if($comment['parentid'] == 0)
{
$comment["children"] = GetChildren($comment['id'], $comments);
$sortedcomments[] = $comment;
}
}
function GetChildren($parentid, $comments)
{
$childcomments = array();
foreach($comments as $comment)
{
if($comment['parentid'] != 0 && $comment['parentid'] == $parentid)
{
$comment["children"] = GetChildren($comment['id'], $comments);
$childcomments[] = $comment;
}
}
return $childcomments;
}