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.

Answer 2

I used a simpler, portion of a native WP in function. hope it helps and some one can continue to develop. Here is a simplified version that shows the title & excerpt of the post along with the comment content & author from the latest commented posts using get_comments.

    $args = array(
        'status' => 'approve',
        'number' => 6,
        'order' => 'DESC'
    );
    $comments = get_comments($args);

    foreach($comments as $comment) : $count++;

            $post_args = array(
                'post_type' => 'post',
                'p' => $comment->comment_post_ID,
                'posts_per_page' => 1
                );

            $posts = get_posts($post_args);

            foreach($posts as $post) : setup_postdata($post);

                the_title();
                the_excerpt();

            endforeach;

        echo $comment->comment_content;     
        echo $comment->comment_author;

    endforeach;

Answer 3

OK guys,

A lot of great answers here, but obviously nobody's taken the time to test them.

Hao Lian gets the credit for the first best original answer, but unfortunately his code doesn't show posts without comments.

Captain Keytar is on the right track, but his code will display every single post and attachment as a separate result.

Here is a modified version of Captain Keytar but it limits the results to the type 'post'.. that has been published (to avoid getting drafts!!)

    select wp_posts.*,
    coalesce(
        (
            select max(comment_date)
            from $wpdb->comments wpc
            where wpc.comment_post_id = wp_posts.id
        ),
        wp_posts.post_date
    ) as mcomment_date
    from $wpdb->posts wp_posts
    where post_type = 'post'
    and post_status = 'publish' 
    order by mcomment_date desc
    limit 10

Answer 4

This is an old question, but I had the same issue and found a much cleaner way to do this, so I'm posting it here in case it helps anyone.

If you use the posts_clauses filter you can then just modify the main query and still use The Loop and all the regular loop functions.

function intercept_query_clauses( $pieces ) {
    global $wpdb;

    $pieces['fields'] = "wp_posts.*,
    (
        select max(comment_date)
        from " . $wpdb->comments ." wpc
        where wpc.comment_post_id = wp_posts.id AND wpc.comment_approved = 1
    ) as mcomment_date";
    $pieces['orderby'] = "mcomment_date desc";

    return $pieces;
}

add_filter( 'posts_clauses', 'intercept_query_clauses', 20, 1 );

Note that I changed the sql slightly for my own purposes, but the general concept is the same.

Answer 5

As an addendum to Hao Lian's answer, if you use the following query:

select wp_posts.*,
coalesce(
    (
        select max(comment_date)
        from $wpdb->comments wpc
        where wpc.comment_post_id = wp_posts.id
    ),
    wp_posts.post_date
) as mcomment_date
from $wpdb->posts wp_posts
order by mcomment_date desc
limit 10

This mixes in posts that don't have comments yet, and sorts them by post_date and max(comment_date).

Answer 6

Code suggested by Hao Lian works perfect except for the fact that we should add the following WHERE clause to avoid pulling POST with comment_count = 0, this situation is caused by spam comments.

The WHERE clause to add is as follows:

WHERE comment_approved = '1' AND comment_type = '' AND post_password = ''

Complete code after adding the where clause shoud look like following:

select wp_posts.*, max(comment_date) as comment_date
from wp_posts 
right join wp_comments on id = comment_post_id
WHERE comment_approved = '1' AND comment_type = '' AND post_password = ''
group by ID    
order by comment_date desc
limit 6