Mac's WordPress Tips

UPDATE: See the excellent variation by keesiemeijer here: https://gist.github.com/4643765

Many times it is necessary to use a filter to modify a query.  For example, you may want to include a column that WP does not normally include in a query.  Or, you may want to do some complex selection of posts based on criteria that query_posts does not supply. 

However, you do not usually want these filters to be active for all queries.  One answer is to use a global variable to hold the additional part of the query and check for this global in the filter function.  Simply reset the variable to null or the empty string to deactivate the filter.

Add these functions to your functions.php:

function mam_posts_fields ($fields) {
   global $mam_global_fields;
   // Make sure there is a leading comma
   if ($mam_global_fields) $fields .= (preg_match('/^(\s+)?,/',$mam_global_fields)) ? $mam_global_fields : ", $mam_global_fields";
   return $fields;
}
function mam_posts_join ($join) {
   global $mam_global_join;
   if ($mam_global_join) $join .= ' ' . $mam_global_join;
   return $join;
}
function mam_posts_where ($where) {
   global $mam_global_where;
   if ($mam_global_where) $where .= ' ' . $mam_global_where;
   return $where;
}
function mam_posts_orderby ($orderby) {
   global $mam_global_orderby;
   if ($mam_global_orderby) $orderby = $mam_global_orderby;
   return $orderby;
}
add_filter('posts_fields','mam_posts_fields');
add_filter('posts_join','mam_posts_join');
add_filter('posts_where','mam_posts_where');
add_filter('posts_orderby','mam_posts_orderby');
?>

Then add code like this in your template:

$mam_global_where = " AND $wpdb->posts.comment_count > 0";

$mam_global_fields = ', intval(wpmeta.meta_value) as sortval';
$mam_global_join = " LEFT JOIN $wpdb->postmeta wpmeta ON
         ({$wpdb->posts}.ID = wpmeta.post_id AND wpmeta.meta_key = 'priority')";
$mam_global_orderby = 'sortval';

Note that the variables for  _fields, _where and _join are appended to the existing clauses in the query, but _orderby replaces the current clause.