Filtering the MAX() function in a JOINED MySQL Query

Filtering the MAX() function in a JOINED MySQL Query

Solved: Here is the solution code.
//Extend Category queries to support "latest_post" for orderby parameter
function filter_term_sort_by_latest_post_clauses( $pieces, $taxonomies,
$args )
{
global $wpdb;
if ( in_array('category', $taxonomies) && $args['orderby'] ==
'latest_post' )
{
$pieces['fields'] .= ", MAX(p.post_date) AS last_date";
$pieces['join'] .= " JOIN $wpdb->term_relationships AS tr JOIN
$wpdb->posts AS p ON p.ID=tr.object_id AND
tr.term_taxonomy_id=tt.term_taxonomy_id";
$pieces['where'] .= " AND p.post_status='publish' GROUP BY
t.term_id";
$pieces['orderby'] = "ORDER BY last_date";
$pieces['order'] = "DESC"; // DESC or ASC
}
return $pieces;
}
add_filter('terms_clauses', 'filter_term_sort_by_latest_post_clauses', 10,
3);
Original question:
I have added the following function & filter hook in a WordPress site that
allows me to list the categories, sorted by the most recent post in each
category. The function works as expected, except that draft posts are
included, and will move that particular category to the top of the list.
//Extend Category queries to support "latest_post" for orderby parameter
function filter_term_sort_by_latest_post_clauses( $pieces, $taxonomies,
$args )
{
global $wpdb;
if ( in_array('category', $taxonomies) && $args['orderby'] ==
'latest_post' )
{
$pieces['fields'] .= ", MAX(p.post_date) AS last_date";
$pieces['join'] .= " JOIN $wpdb->term_relationships AS tr JOIN
$wpdb->posts AS p ON p.ID=tr.object_id AND
tr.term_taxonomy_id=tt.term_taxonomy_id";
$pieces['where'] .= " GROUP BY t.term_id";
$pieces['orderby'] = "ORDER BY last_date";
$pieces['order'] = "DESC"; // DESC or ASC
}
return $pieces;
}
add_filter('terms_clauses', 'filter_term_sort_by_latest_post_clauses', 10,
3);
I would like to be make the select clause "MAX(p.post_date) AS last_date"
only include values from published posts ( WHERE p.post_status=publish" )
How can I accomplish this?
Thanks!