Created
September 28, 2010 21:38
-
-
Save mikeschinkel/601838 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?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"; | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
http://core.trac.wordpress.org/ticket/14997