Sort Posts by Comment Count in WordPress

Published: January 20th, 2009 by:

I use this technique to show the most popular posts on our homepage, and although this isn't a core feature in Wordpress, it's not all that hard to do. All it takes is a little PHP and a single edit to one of your Wordpress template files.


I decided not to use a plugin because I find it easier to just edit the templates directly. Here is the code:

<?php
$results = $wpdb->get_results("SELECT ID, comment_count FROM wp_posts WHERE post_type = 'post' && post_status = 'publish' ORDER BY comment_count DESC LIMIT 5");
foreach ($results as $r):
	query_posts(array('p' => $r->ID));
	while (have_posts()):
		the_post();
?>
<!-- regular wordpress loop code goes here -->
<?php endwhile; endforeach; ?>

This code manually calls a SQL query because there is no template tag that supports this method of sorting as of WordPress 2.7. We are only querying for the post IDs because we will use that ID to call up the WordPress loop which will allow us to use the normal template tags inside the manual WordPress loop.


7 Responses to “Sort Posts by Comment Count in WordPress”

  • kristarella

    Thanks! Just what I needed.

    I couldn’t get it to work until I changed FROM wp_posts to FROM $wpdb->posts. I’m not sure if that’s an error or perhaps a quirk because I’m using WPMU.

    Cheers.

     

  • Marc

    Many thanks, especially for not sticking it in a plugin.

     

  • moyo

    Any ideas how to make this SQL for posts current category?

     

  • moyo

    Looks like I’ve found the solution by myself. Anyway thanks for your post.

     

  • Michael Fields

    Hi I stumbled upon your site today while researching if this was possible. I found out that it was possible to accomplish this task without the use of a custom query. Here’s the solution that I came up with:

    add_filter( ‘pre_get_posts’, ‘order_posts_by_comment_count’ );
    function order_posts_by_comment_count( $query ) {
    if ( is_home() )
    $query->set( ‘orderby’, ‘comment_count’ );
    $query->set( ‘order’, ‘DESC’ );
    return $query;
    }
    best wishes,
    -Mike

     

     

  • Ted

    I hope you can help me.  I need to create a page that sort posts by recent comment, not by comment count in WordPress.  Many Thanks…
    Ted

     

  • Brett Sanders

    Hi, this is exactly what I am after. Thanks.
    I was just wondering what template you need to edit for this?
    If you can help me at all that would be great.

     

Leave a Reply





Wordpress doesn't like it when you post PHP code. Go save your code at pastebin, and post the link here.

About the Author

Andrew has been coding PHP applications since 2006, and has plenty of experience with PHP, MySQL, and Apache. He prefers Ubuntu Linux on his desktop and has plenty of experience at managing CentOS web servers. He is the owner of Wells IT Solutions LLC, and develops PHP applications full time for anyone that needs it as well as does desktop computer support locally in the local area. He spends most of his free time exploring new programming concepts and posting on The Webmaster Forums.