php - Ordering Wordpress posts by most recent comment

Question

I'm wanting to order Wordpress posts by the most recent comment. To the best of my knowledge this isn't possible using the WP_Query object, and would require a custom $wpdb query, which I can easily write. However, I then don't know how to setup the loop to run off this object.

Can anyone help?

Answer 1

Assign

select wp_posts.*, max(comment_date) as max_comment_date
from $wpdb->posts wp_posts
right join $wpdb->comments
on id = comment_post_id
group by ID
order by max_comment_date desc
limit 10

to some variable $query. You can fiddle around with the 10 or the query itself. (I'm no SQL optimization ninja.) Then your code will look something like

<?php
     $results = $wpdb->get_results($query) or die('!');
     foreach ($results as $result):
?>
[insert template here]
<?php endforeach ?>

This pattern is covered in more depth by the Codex.