php - How to sort a 'query_posts' function by custom field, while limiting posts by another custom field

Question

I'm querying a series of posts in WP with the following function:

<?php 
$thirtydays = date('Y/m/d', strtotime('+30 days'));
$paged = (get_query_var('paged')) ? get_query_var('paged') : 1;
query_posts( array( 
    'post_type' => array('post', 'real-estate'), 
    'meta_key' => 'Time          Available', 
    'meta_compare' => '<=', 
    'meta_value' => $thirtydays, 
    'paged' => $paged )); 
?>

This part is working fine. It's basically pulling all my Real Estate posts, but only returning results that have a 'Time Available' of 30 days or less.

I need this to also order the posts in ascending order from low to high using the data from another custom field, 'Price.'

Whenever I add the standard 'orderby' => 'meta_value', 'meta_key' => 'Price' it no longer shows results within 30 days.

Is there any way I can combine these two? And is it possible to add a button which re-runs the query and sorts by Price, Bedrooms, etc? Or is this too specific for WP?

Answer 1

I believe this will provide you want you need. It's a class called PostsOrderedByMetaQuery that extends WP_Query and accepts new arguments 'orderby_meta_key' and 'orderby_order':

class PostsOrderedByMetaQuery extends WP_Query {
  var $posts_ordered_by_meta = true;
  var $orderby_order = 'ASC';
  var $orderby_meta_key;
  function __construct($args=array()) {
    add_filter('posts_join',array(&$this,'posts_join'),10,2);
    add_filter('posts_orderby',array(&$this,'posts_orderby'),10,2);
    $this->posts_ordered_by_meta = true;
    $this->orderby_meta_key = $args['orderby_meta_key'];
    unset($args['orderby_meta_key']);
    if (!empty($args['orderby_order'])) {
      $this->orderby_order = $args['orderby_order'];
      unset($args['orderby_order']);
    }
    parent::query($args);
  }
  function posts_join($join,$query) {
    if (isset($query->posts_ordered_by_meta)) {
      global $wpdb;
      $join .=<<<SQL
INNER JOIN {$wpdb->postmeta} postmeta_price ON postmeta_price.post_id={$wpdb->posts}.ID
       AND postmeta_price.meta_key='{$this->orderby_meta_key}'
SQL;
    }
    return $join;
  }
  function posts_orderby($orderby,$query) {
    if (isset($query->posts_ordered_by_meta)) {
      global $wpdb;
      $orderby = "postmeta_price.meta_value {$this->orderby_order}";
    }
    return $orderby;
  }
}

You would call it like this:

$thirtydays = date('Y/m/d', strtotime('+30 days'));
$paged = (get_query_var('paged')) ? get_query_var('paged') : 1;
$query = new PostsOrderedByMetaQuery(array(
  'post_type' => array('post', 'real-estate'),
  'meta_key' => 'Time Available',
  'meta_compare' => '<=',
  'meta_value' => $thirtydays,
  'paged' => $paged,
  'orderby_meta_key' => 'Price',
  'orderby_order'    => 'DESC',
));
foreach($query->posts as $post) {
  echo " {$post->post_title}
";
}

You can copy the PostsOrderedByMetaQuery class to your theme's functions.php file, or you can use it within a .php file of a plugin you may be writing.

If you want to test it quickly I've posted a self-contained version of the code to Gist which you can download and copy to your web server's root as test.php, modify for your use case, and then request from your browser using a URL like http://example.com/test.php.

Hope this helps.

-Mike

P.S. This answer is very similar to an answer I just gave over at WordPress Answers, which is the sister site of StackOverflow where lots of WordPress enthusiasts like me answer questions daily. You might want to see that answer too because it has a tad more explanation and because you might want to see WordPress Answers. Hope you'll consider posting your WordPress questions over there too in the future?