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

7 thoughts on “Improving P2 — Order Posts By Last Comment Date

  1. Is it possible to group posts in p2 by dates? Todays post, yesterday posts, post on 15 july etc?

  2. I have been looking for the same thing for the same template! Found this site without writing “P2″ in Google;). How to implement this?

  3. Ehi, what about creating a plugin for wordpress? If you don’t want to do it, can I do it for you? It is a great piece of software, it were days I was looking something like that.

    Thank you very much.

    Here it is the code (you can copy and past this code in the plugin directory, name the file p2plus.php):

    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);

    ?>

    • Sorry, I paste only a piece. Here it is the full code (I hope…)

      ?php
      /**
      * @package p2plus
      * @version 0.1
      */
      /*
      Plugin Name:p2plus
      Plugin URI: http://maisonbisson.com/blog/post/13758/improving-p2-order-posts-by-last-comment-date/
      Description: Order posts by their comments date. I created a plugin starting from the good idea of Casey Bisson, all the code comes from him.
      Author: Casey Bisson
      Version: 0.1
      Author URI: http://maisonbisson.com/blog/post/13758/improving-p2-order-posts-by-last-comment-date/
      */

      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);

      ?>

  4. Hello, thank you for this code snippet.

    Unfortunately, it contains a bunch of HTML that renders it unusable in functions.php.

    The real code should be:


    /* Comments by recent date. */
    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);

    without any of the in it.

    Then you can post it into functions.php and it should take over your post order. How can you handle the case of sticky posts, however?

Comments are closed.