Algorithm To Sort Child Comments

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;
}

 

Related Articles

Related Questions

Whirlpool Oven F6E6: Appliance Manager 1 Board Communication

I have a brand new Whirlpool oven W11I OM1 4MS2 H or (859991549450). I bought it alongside the microwave combi oven. I have had...

Whats the difference between the Tapo P100 and the P105?

There are a few different Tapo smart plugs. The P100 and P110 differ based on the smart power monitoring feature but where does the...

Time remaining keeps going up on Whirlpool dryer??

I have clothes in the dryer regularly and the time remaining shows on the display. There could be 10 mins remaining and if I...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Latest Tools

Memory Converter

Converting values between various metric measurements is usually quite simple as there will be 1000 of the smaller unit in the next larger unit....

Bitrate Converter

Below you will find a bitrate converter. This tool will allow you to enter a bitrate value, in one of many different formats and...

Aesthetic Text Generator

There are various ways to make your social media profile seem more unique, some of which are not as easy to implement as others....

Aspect Ratio Calculator For Images

Aspect ratio is the ratio between the height and width of an image. If you want to resize an image by 100 pixels, you...

Add Text To Image

Use this free tool to add text to an image. Simply select the image file that you want to overlay text onto and you...

JavaScript Multi-line String Builder

Javascript did not always support multi-line strings. If you attempted to create a string variable using quotes, putting a line break into the source...

Latest Posts

Latest Questions