posts-by-latest-comment.php

<?php
/*
  PostsByLatestCommentQuery class that extends WP_Query and sorts posts by latest comment

  Author: Mike Schinkel (http://mikeschinkel.com)

   Just drop this example into the root of your website and call directly to see it work.
   Use the class in your plugins or themes.

   See: http://lists.automattic.com/pipermail/wp-hackers/2010-September/035113.html

*/

header('Content-type:text/plain');
include "../wp-load.php";

class PostsByLatestCommentQuery extends WP_Query {
	function __construct($args=array()) {
		$this->PostsByLatestCommentQuery($args);
	}
	function PostsByLatestCommentQuery($args=array()) {
		$this->posts_by_latest_comment = true;
		parent::query($args);
	}
	static function on_load() {
		add_filter('posts_where',array(__CLASS__,'posts_where'),10,2);
		add_filter('posts_join',array(__CLASS__,'posts_join'),10,2);
		add_filter('posts_groupby',array(__CLASS__,'posts_groupby'),10,2);
		add_filter('posts_orderby',array(__CLASS__,'posts_orderby'),10,2);
		add_filter('posts_fields',array(__CLASS__,'posts_fields'),10,2);
	}
	static function posts_where($where,$query) {
		if (isset($query->posts_by_latest_comment)) {
			global $wpdb;
			$where .= " AND {$wpdb->comments}.comment_approved='1'";
		}
		return $where;
	}
	static function posts_join($join,$query) {
		if (isset($query->posts_by_latest_comment)) {
			global $wpdb;
			$join .= " INNER JOIN {$wpdb->comments} ON {$wpdb->comments}.comment_post_ID={$wpdb->posts}.ID ";
		}
		return $join;
	}
	static function posts_groupby($groupby,$query) {
		if (isset($query->posts_by_latest_comment)) {
			global $wpdb;
			$groupby = " {$wpdb->comments}.comment_post_ID ";
		}
		return $groupby;
	}
	static function posts_orderby($orderby,$query) {
		if (isset($query->posts_by_latest_comment)) {
			global $wpdb;
			if (!empty($orderby))
				$orderby = ",{$orderby}";
			$orderby = "MAX({$wpdb->comments}.comment_date_gmt) DESC{$orderby}";
		}
		return $orderby;
	}
	static function posts_fields($fields,$query) {
		if (isset($query->posts_by_latest_comment)) {
			global $wpdb;
			$fields = <<<SQL
MAX({$wpdb->comments}.comment_date) AS comment_date,
MAX({$wpdb->comments}.comment_date_gmt) AS comment_date_gmt,
COUNT({$wpdb->comments}.comment_ID) AS comment_count,
{$fields}
SQL;
		}
		return $fields;
	}
}
PostsByLatestCommentQuery::on_load();

$query = new PostsByLatestCommentQuery('post_type=post');
foreach($query->posts as $post) {
	echo "{$post->comment_date} ({$post->comment_count}) -- {$post->post_title}\n";
}

Yeah, I've been down this route too:

http://plugins.trac.wordpress.org/browser/scb-framework/tags/1.6/scb/Query.php

Then I found an alternative approach:

http://plugins.trac.wordpress.org/browser/scb-framework/tags/r24/scb/QueryManipulation.php

This is how you use it:

function my_query_manipulation( $bits, $wp_query ) {
  $bits['where'] .= ' AND foo = "bar"';
  $bits['orderby'] = 'MONTH(post_date) DESC'

  return $bits;
}

new scbQueryManipulation('my_query_manipulation');

Note that it will only fire for the next query that occurs.

Interesting. Seems you've got a layer of abstraction compared to what I wrote. I'll have to run your through the debugger the first chance I get so I can wrap my head around it. Thanks for sharing.

-Mike

Yeah, so in the first version, you're supposed to extend the scbQuery class. Any public method that doesn't begin with an '_' is automatically added as a filter.

I gave up on that because, in each method, you have to duplicate any check you want to make against $wp_query.

You know it would seem to make some sense to have a hook in WordPress' query that would pass in all the options like you are passing to your callback as a single hook rather than calling seven different hooks. It would make it so much easier in general (i.e. people wouldn't have to find your code, it would be in core.) Wonder if there is appetite to consider adding such a hook that could eventually be used to override code like this in query?

if ( !$q['suppress_filters'] ) {
    $where      = apply_filters_ref_array( 'posts_where_paged', array( $where, &$this ) );
    $groupby    = apply_filters_ref_array( 'posts_groupby',     array( $groupby, &$this ) );
    $join       = apply_filters_ref_array( 'posts_join_paged',  array( $join, &$this ) );
    $orderby    = apply_filters_ref_array( 'posts_orderby',     array( $orderby, &$this ) );
    $distinct   = apply_filters_ref_array( 'posts_distinct',    array( $distinct, &$this ) );
    $limits     = apply_filters_ref_array( 'post_limits',       array( $limits, &$this ) );
    $fields     = apply_filters_ref_array( 'posts_fields',      array( $fields, &$this ) );
}

http://core.trac.wordpress.org/ticket/14997