SQL to sort nested comments in wordpress by likes

Question

I hope some kind person can help me out here.

I want to sort nested comments in wordpress by likes. I have only found one plugin that does this and it doesn't meet my needs, so I'm attempting to write my own. Most of it is actually pretty straightforward, but the sql is eluding me (not really my strength).

I need an SQL Query to sort comments by likes, with replies immediately following their parent, and replies to each parent also sorted by likes. Top level comments and replies are differentiated by 'layer'. There is only one level of replies. My table looks like this:

ID (Int)

Comment_Name (VarChar)

Layer (Int)... 1 for top level comment, 2 for reply

ID_of_Parent_Comment (Int)... replys must be grouped under top level comment with this id

Likes (Int)

For example, if top level comments are represented by numbers and replies by letters, it would look something like this:

1, 2, 3, 3a, 3b, 4, 5, 5a... etc

Anyone have any ideas?

Answer 1

It turns out that the other answer did not quite work out after all. It sure looked right. Replies were grouped nicely beneath the appropriate parent comment, everthing was sorted by likes. But if you look closely, the sqlfiddle test returned 14 records where there are only 12 available.

After spending way too much time fiddling with it on my site, I couldn't resolve it any further. One group or the other (top level comments or replies) were always either left off or duplicated.

I finally gave up, assuming that it could not be done with SQL, so I went back to something I was familiar with: php. Here is my solution. Hopefully someone will find it useful. If nothing else, it was a fun project.

myComments.php

<?php

global $wpdb;

$post_ID = get_the_ID();

// Get Comment Table
$sql =
 " SELECT *"
." FROM wp_comments"
." WHERE comment_post_ID = " . $post_ID     // only retrieve comments for this post
." AND comment_parent = '0'"                // only retrieve top level comments
." ORDER BY likes DESC"
.";";
$tlc = $wpdb->get_results($sql, ARRAY_A);   // Retrieve all records into $tlc
                                            // this should never be
                                            // large enough to be a problem.
$commentCount = count( $tlc );              // Number of TopLevelComments

// Adjust Comments
for ( $i = 0; $i <= $commentCount-1; $i++ ) {
    $tlc[$i]['layer'] = 0;                  // Layer 0 indicates top level comment
    $tlc[$i]['index'] = $i;                 // index is used to group parents 
                                            // with children
}

// Get Reply Table
$sql =
 " SELECT *"
." FROM wp_comments"
." WHERE comment_post_ID = " . $post_ID
." AND comment_parent > '0'"                        // only retrieve replies
." ORDER BY likes DESC"
.";";
$replies = $wpdb->get_results($sql, ARRAY_A);
$replyCount = count( $replies );

// Adjust Replies
for ( $i = 0; $i <= $commentCount-1; $i++ ) {
    $replies[$i]['layer'] = 1;                      // Layer 1 indicates replies
}

// Set child index to that of parent
// then add child record to parent array
for ( $i = 0; $i <= $replyCount-1; $i++ ) {
    $x = $replies[$i]['comment_parent'];            // Get ID of parent
    for ( $j = 0; $j <= $commentCount-1; $j++ ) {
        if ( $tlc[$j]['comment_ID'] == $x ) {       // If parent found
            $value = $tlc[$j]['index'];             // Get parent's index
            $replies[$i]['index'] = $value;         // Give child parent's index
            array_push ( $tlc, $replies[$i]);
        }
    }
}

// Sort comments
// Note that $tlc was sorted by select
// and index was assigned while in that order
$tlc = array_orderby($tlc,  'index', SORT_ASC, 
                            'layer', SORT_ASC,
                            'likes', SORT_DESC);

// Display comments
$commentCount = count($tlc);
if ( $commentCount ) {
    echo "<ol class='commentNumbering'>";
    // Used to determine if we have opened a second <ol> for nested comments
    // and ensure we close it before we are done.
    $inReplyList = false;
    // We don't want to close the <ol> before we've opened it.
    $firstComment = true;
    for ( $i = 0; $i <= $commentCount-1; $i++ ) {
        $myComment = $tlc[$i];
        // Set $depth (needed by reply-link on myCommentTemplate page)
        $depth = 0;
        $comment_ID = $myComment['comment_ID'];
        while( $comment_ID > 0  ) {
            $tempComment = get_comment( $comment_ID );
            $comment_ID = $tempComment->comment_parent;
            $depth++;
        }
        // Treat each group of nested comments as a separate ordered group
        if ( $depth == 2 ) {
            if ( ! $inReplyList ) {
                echo "<ol>";
                $inReplyList = true;
            }
        } else {
            if ( ! $firstComment ) {
                if ( $inReplyList ) {
                    echo "</ol>";
                    $inReplyList = false;
                }
            }
        }
        $firstComment = false;
        // Display each comment
        include ('myCommentTemplate.php');
    }
    if ( $inReplyList ) {
        echo "</ol>";
    }
    echo "</ol>";
} else {
    echo 'No comments found.';
}
// Where comments are made
include('myCommentForm.php');

$wpdb->flush();

?>

function array_orderby() (located in functions.php)

/* SORT PHP ARRAYS OF RECORDS */

// PHP function 'array_multisort' requires columns //
// This function handles the conversion from row to col and back again //

// Example:
// $sorted = array_orderby($data, 'volume', SORT_DESC, 'edition', SORT_ASC);

function array_orderby()
{
    $args = func_get_args();
    $data = array_shift($args);
    foreach ($args as $n => $field) {
        if (is_string($field)) {
            $tmp = array();
            foreach ($data as $key => $row)
                $tmp[$key] = $row[$field];
            $args[$n] = $tmp;
            }
    }
    $args[] = &$data;
    call_user_func_array('array_multisort', $args);
    return array_pop($args);
}

Answer 2

Looks like this should be close:

select 
  post.ID,
  post.likes as postLikes,
  reply.ID,
  reply.likes as replyLikes
from MyTable post
  left join MyTable reply
    on post.ID = reply.ID_of_Parent_Comment
where post.ID_of_Parent_Comment is  null
order by post.likes desc, reply.likes desc
;

It will give you the parent ID's sorted by parent likes and the related child ID's for each parent (if any) sorted by most liked child