Improving P2 — Order Posts By Last Comment Date

I’m a big fan of the P2 theme for WordPress. It makes it dead easy anybody familiar with WordPress to host a discussion site and improve collaboration across time and distance.

That said, one feature I’d like to see is the ability to order the posts by the last comment date, rather than post date. When we started using P2 to power a workgroup discussion last year, I wrote a bit of code to sort the posts that way, here’s how:

function p2plus_posts_fields( $query ) {
global $wpdb;
 return( $query .", IF( $wpdb->comments.comment_date, MAX( $wpdb->comments.comment_date ), $wpdb->posts.post_date ) AS last_comment_date " );
}

function p2plus_posts_join( $query ) {
 global $wpdb;
 return( $query ." LEFT JOIN $wpdb->comments ON $wpdb->comments.comment_post_id = $wpdb->posts.ID " );
}

function p2plus_posts_groupby( $query ) {
 global $wpdb;
 return( " $wpdb->posts.ID ". $query );
}

function p2plus_posts_orderby( $query ) {
 return( ' last_comment_date DESC, '. $query );
}

function p2plus_pre_get_posts( $query ) {
 if( is_home() || is_front_page() ){
 add_filter( 'posts_fields', 'p2plus_posts_fields', 7 );
 add_filter( 'posts_join', 'p2plus_posts_join', 7 );
 add_filter( 'posts_groupby', 'p2plus_posts_groupby', 7 );
 add_filter( 'posts_orderby', 'p2plus_posts_orderby', 7 );
 }
}
add_action('pre_get_posts', 'p2plus_pre_get_posts', 10);

This results in page of posts ordered by comment date a query MySQL that looks like this:

SELECT SQL_CALC_FOUND_ROWS mistime_posts.*, IF( mistime_comments.comment_date, MAX( mistime_comments.comment_date ), mistime_posts.post_date ) AS last_comment_date
FROM mistime_posts
LEFT JOIN mistime_comments ON mistime_comments.comment_post_id = mistime_posts.ID
WHERE 1=1
AND mistime_posts.post_type = 'post'
AND (mistime_posts.post_status = 'publish' OR mistime_posts.post_status = 'private')
GROUP BY mistime_posts.ID
ORDER BY mistime_posts.post_date DESC
LIMIT 0, 20

Menu