Ordering posts by most recently commented

<?php
$ids = $wpdb->get_col("SELECT DISTINCT comment_post_ID
FROM $wpdb->comments
ORDER BY comment_date DESC
LIMIT 0 , 30");

foreach ($ids as $id) {
  $post = &get_post( $id );
  setup_postdata($post); ?>
  <p><a href="<?php the_permalink() ?>" rel="bookmark" title="Permanent Link to <?php the_title_attribute(); ?>"><?php the_title(); ?></a></p>
  <?php
}
?>

Thanks Michael. But that gives me the latest post (which has no comments) repeated 30 times? I’m not sure why.

Hmm–not sure what the problem is…

If you use phpMyAdmin with this SQL do you get a list of ids returned?

SELECT *
FROM wp_comments
ORDER BY wp_comments.comment_date DESC
LIMIT 0 , 30

Note: assumes your comments table is called wp_comments so change accordingly

Yes I do get a list of posts. Let me try this some more.

But here’s the thing: I’m looking for a slightly different query. What I’m trying to figure out is how to get a list of post in a category, which are sorted in chronological order, where posts with new comments going to the top of the order.

This is how most forums work. If I write a new post, it goes to the top of the list, and works its way down as posts are added (and if no one comments on my post). If someone comments on my post, it goes to the top again.

Well the category requirement is new and you could exclude posts in whatever category, but besides the category restricting, unless I’m missing it, that code does what you want…

What it’s missing is that I want to include new posts with no comments.

That query shows posts with recent comments. It doesn’t include new posts with no comments.

So take a look at this page:
https://www.ads-software.com/support/forum/3

New posts show up at the top, even with no replies. And then if a post has a reply, it also shows up at the top. Most message boards work this way.

Ok so part of the problem with your query is it returns everything – drafts, pages, posts, everything.

So the first guy was on the right track, here’s what I ended up with:

SELECT $wpdb->posts.*,
coalesce (
    (
        select max(comment_date)
        from $wpdb->comments wpc
        where wpc.comment_post_id = $wpdb->posts.id AND wpc.comment_approved = '1'
    ),
    $wpdb->posts.post_date
) as mcomment_date
from $wpdb->posts WHERE $wpdb->posts.post_status = 'publish' AND $wpdb->posts.post_type = 'post'
order by mcomment_date desc
limit 6"