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";
}

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