Support » Fixing WordPress » How to list users order by comment count

  • Resolved Elena

    (@elenapanzalis)


    hi,
    I try to display a list of all the user order by their comment count.
    I found something on the web but all the solution I found didn’t help with the “orderby”.

    one example is:
    https://wordpress.org/support/topic/registered-users-comment-count?replies=10

    <?php
    global $wpdb;
    $where = 'WHERE comment_approved = 1 AND user_id <> 0';
    $comment_counts = (array) $wpdb->get_results("
            SELECT user_id, COUNT( * ) AS total
            FROM {$wpdb->comments}
            {$where}
            GROUP BY user_id
        ", object);
    
        echo '<ul>';
    
        foreach ( $comment_counts as $count ) {
          $user = get_userdata($count->user_id);
        //echo "<pre>"; print_r($count); echo "</pre>";
        //echo "<pre>"; print_r($user); echo "</pre>";
          echo '<li>User ' . get_avatar( $user->ID ) . "<a href='" . get_author_posts_url($user->ID) . "'>" . $user->display_name . "</a>" . ' comment count is ' . $count->total . "</li>" . '
        ';
        }
        echo '</ul>';
        ?>

    With this code I can see the users list with the comment count near the user name. But how can I sort the list by comment count?

    Second but less important: I added to the code the thumb and the author link.. do you think it is correct how I did it?

    Thank you in advance

    Elena

Viewing 2 replies - 1 through 2 (of 2 total)
  • You just need to add in an ORDER BY clause:

    $comment_counts = (array) $wpdb->get_results("
            SELECT user_id, COUNT( * ) AS total
            FROM {$wpdb->comments}
            {$where}
            GROUP BY user_id
            ORDER BY total
        ", object);

    or ORDER BY total DESC if you want them in reverse order.

    Elena

    (@elenapanzalis)

    Thank you very much! It works perfectly! 😉

Viewing 2 replies - 1 through 2 (of 2 total)
  • The topic ‘How to list users order by comment count’ is closed to new replies.