Filters to Modify a Query

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.

9 Responses to Filters to Modify a Query

  • Denis says:

    Hello,

    I think this is exactly what I am looking for, but I don’t understand how to use it the right way.
    I want to create a “most Popular” page on my site. Cause I am using HEADWAY its not possible to change the default wordpress query.

    So, I created a custom field in my posts “view_counter”.

    How do I use this custom field “view_counter” in this function? Is it only in the ORDER BY?

    I need some help, advice here,
    Cheers,
    Denis

    • Mac McDonald says:

      Actually, I think you might be able to do what you want by using Custom Field Parameters.

      You would specify these parameters in addition to any others required:

      'meta_key' => 'view_counter',
      'orderby' => 'meta_value_num',
      'order' => 'DESC'

      • Denis says:

        Hey Mac,

        I cant use the Custom Field Parameters, cause I dont want to touch the code of my template.
        I like to do it via the filter in a functions.php.

        It must be something like ODER BY view_counter DESC

        But how will I write this in the funtions.php

        Cheers,
        Denis

        • Mac McDonald says:

          Even the filters will require some changes to the template. I am not sure there is a method to do this without changing the template.

  • Beee says:

    I’m trying to order by 2 custom fields.
    I have tried the above example, but can’t get it to work….
    I can’t find where to add both custom fields I want to use to order by.

    • macadmin says:

      You will need to modify the fields, join, and orderby values. This has not been tested, but should be close:

      $mam_global_fields = ', wpmeta1.meta_value as sortval1, wpmeta2.meta_value as sortval2 ';
      $mam_global_join = " LEFT JOIN $wpdb->postmeta wpmeta1 ON
               ({$wpdb->posts}.ID = wpmeta1.post_id AND wpmeta1.meta_key = 'priority'),
              LEFT JOIN $wpdb->postmeta wpmeta2 ON
                ({$wpdb->posts}.ID = wpmeta2.post_id AND wpmeta2.meta_key = 'second key')";
      $mam_global_orderby = 'sortval1, sortval2';
      

      If your meta_values are numbers, you need to force the sortvals to be numeric. One way to do this is just to add a zero to them. In that case, the orderby could look like this:

      $mam_global_orderby = 'sortval1 + 0, sortval2 + 0';
      
  • keesiemeijer says:

    Wow, very cool. Never thought about doing it this way. I made a slight alteration to the code here: https://gist.github.com/4643765 . This way you can put the filter directly in the query and you don’t need global variables. Thanks for all the great tips on this site.

Leave a Reply

Your email address will not be published. Required fields are marked *


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>