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

Function Keys Reversing Between Fn Actions And Normal

My keyboard has the usual F1 to F12 keys along the top. I use these for shortcuts in various applications. These keys also have...

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...

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

AI Content Detector

We've got this awesome free tool that'll help you figure out if that content you're looking at was written by a human or some...

Image Saturation

Are you looking for an easy-to-use, free app to modify your image saturation levels and make your pictures truly pop? Look no further! Our...

Pixelate Image Tool

Introducing the ultimate free online image pixelator tool that allows you to easily transform your images into stunning pixel art in just a few...

Image RGB Level Adjustment Tool

Introducing the ultimate image color adjustment tool for all your photo editing needs. Our free online tool lets you take full control of your...

Image Color Inverter

Looking for a quick and efficient way to convert your images into negatives? Our Free Image to Negative Converter is the answer! Our online...

Negative Image to Color Image Converter

Welcome to our Negative Image to Color Image Converter, a free and easy-to-use tool that helps you convert your old negative images into vibrant,...

Latest Posts

Latest Questions