Sort Posts by Comment Count in Wordpress
Published: January 20th, 2009 by: Andrew
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.
Related posts:


kristarella
Jun 1st, 2009
10:15 am
Thanks! Just what I needed.
I couldn’t get it to work until I changed
FROM wp_poststoFROM $wpdb->posts. I’m not sure if that’s an error or perhaps a quirk because I’m using WPMU.Cheers.
Marc
Jul 21st, 2009
2:25 pm
Many thanks, especially for not sticking it in a plugin.
moyo
Sep 18th, 2009
2:13 pm
Any ideas how to make this SQL for posts current category?
moyo
Sep 20th, 2009
1:54 am
Looks like I’ve found the solution by myself. Anyway thanks for your post.