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

Its pretty simple and just a single line of code change will do that. I wrote a post some time back about this. Please check if you are interested.

Edit: I think I got your question wrong :). I thought you want to display the most recent comment in a post. Sorry about that.

Answer 2

Assign

select wp_posts.*, max(comment_date) as comment_date
from $wpdb->posts wp_posts
right join $wpdb->comments
on id = comment_post_id
group by ID
order by 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 3

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 4

I'm thinking that adding in the max function will screw up your results. MySQL isn't going to pull the max from each one. It's going to pull the max from the full set. This is the query that'll get you your results:

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

After that, if you want to follow WP convention, use this, and then you can use the functions that most of your templates are using (based on the loop):

$results = $wpdb->get_results($query) or die('!');
     foreach ($results as $post):
    setup_postdata($post);

Answer 5

HI Guys,

This is exactly what I am trying to do but I am confused about how to integrate it. I know php quite well but do not really understand mysql that well. On my front page, www.reviewrecruiter.com.au I am trying to show the last 5 posts by most recent comment according to a specific category.

The following looks like a sql query ;

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

It would appear as though; "$results = $wpdb->get_results($query) or die('!'); foreach ($results as $post): setup_postdata($post);"

Is calling the query right? But where does the sql query go? How do I go about setting it up?

Would be very thankful for any help?

Thanks.

Answer 6

Code suggested by Hao Lian works perfect except the fact that we should add the following WHERE clasue 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

Answer 7

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

Does anybody know how to not hard-code the limit in this code, but rather use the global setting that's set in the WP admin?