Order Wordpress Posts by Recent Comments

Question

I want to order Wordpress posts with the last comment. To my knowledge, this is not possible using the WP_Query object, and it will require a custom $ wpdb query, which I can easily write. However, I then do not know how to configure the loop to run this object.

Can anyone help?

Answer 1

Ok guys

There are many great answers here, but obviously no one had time to test them.

Hao Liang receives credit for the first best original answer, but, unfortunately, his code does not show messages without comments.

Captain Keitar is on the right track, but his code will display each individual post and attachment as a separate result.

Here is a modified version of Captain Keytar, but it limits the results to the type of "post" .. that was 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 2

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 play using 10 or the request itself. (I'm not a SQL optimization ninja). Then your code will look something like this:

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

This template is described in more detail using Codex .

Answer 3

I used the simpler part of the WP built-in function. hope this helps and someone can continue to grow. Here is a simplified version that shows the title and excerpt of the message along with the comment and the author of the latest comments 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 4

As an addition to Hao Lian, 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 up in posts that have no comments yet, and sorts them by post_date and max (comment_date).

Answer 5

This is an old question, but I had the same problem, and I found a much cleaner way to do this, so I post it here if it helps someone.

If you use the posts_clauses filter, you can just change the main query and still use The Loop and all the usual 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 ); 

Please note that I changed sql a bit for my purposes, but the general concept is the same.

Answer 6

The code suggested by Hao Lian works just fine, except that we have to add the following WHERE clause so as not to stretch the 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 = '' 

The full code after adding the where shoud clause is as follows:

 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

I think adding to the max function can ruin your results. MySQL is not going to get the most out of everyone. He's going to get the most out of the full set. This is a query that will give 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 the WP convention, use this, and then you can use the functions that most of your templates use (based on the loop):

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

Answer 8

Get 3 new comments for your personalized question type regardless of approval:

 global $wpdb; $results = $wpdb->get_results( " SELECT wp_posts.ID, MAX(comment_date) AS max_comment_date FROM wp_posts RIGHT JOIN wp_comments ON id = comment_post_id WHERE wp_posts.post_type = 'question' AND wp_posts.post_status = 'publish' GROUP BY ID ORDER BY max_comment_date DESC LIMIT 3 " ); foreach ($results as $result) { $posts_arr[] = $result->ID; } $args = array( 'post_type' => 'question', 'post__in' => $posts_arr, 'orderby' => 'post__in', ); $the_query = new WP_Query( $args ); 

Answer 9

This can be done by combining WP_Comment_Query with WP_Query, for example:

 // For performance, limit the number of queried comments, // but make it be something big enough to account for "duplicate" posts. $comments_query = new WP_Comment_Query; $comments = $comments_query->query( array( 'number' => '100' ) ); if ( $comments ) { foreach ( $comments as $comment ) { // You'll want to convert the dates from string to integer so you can sort them out later $comment_utf = strtotime($comment->comment_date); // Build an array of post IDs with the date of the last published comment $latest_comments[$comment->comment_post_ID] = $comment_utf; }} // Sort the array by date arsort($latest_comments); foreach ($latest_comments as $key => $value) { $posts_ordered[] = $key; } // The nice thing is that WP_Query will remove duplicates by default $args = array ( 'posts_per_page' => '10', 'post__in' => $posts_ordered, 'orderby' => 'post__in'); $query = new WP_Query( $args ); if ( $query->have_posts() ) { while ( $query->have_posts() ) { $query->the_post(); // Do your stuff (add the template or whatever) // If you want to add the comment itself, use this: $comments = get_comments(array('number' => '1', 'post_id' => $post->ID)); foreach($comments as $comment) : echo $comment->comment_content; endforeach; // That about it }} wp_reset_postdata();