How do I order by multiple custom fields using wp_query?

Question

I have a rating system where the user can enter a custom field of either "Stars" or "Hearts", but whichever one they choose they have to enter a value of 1 thru 5. Using wp_query, I can successfully sort by one of these custom fields, but I'm not sure how to sort by both. Here is the code I'm using to sort by one of the custom fields "Stars":

$args=array(
'posts_per_page' => $con_review_num,
'post_type' => 'con_product_reviews',
'paged' => $paged,
'orderby' => 'meta_value',
'order' => 'DESC',
'meta_key' => 'Stars'   
);

Here is what I wish this function would do (notice the last line):

$args=array(
'posts_per_page' => $con_review_num,
'post_type' => 'con_product_reviews',
'paged' => $paged,
'orderby' => 'meta_value',
'order' => 'DESC',
'meta_key' => 'Stars Hearts'    
);

Is there another way to do that?

Answer 1

I had the same problem and could not find a way to make this work with wp_query alone. Instead, I caught the posts_clauses filter and stuck my order_by there. This also meant doing a few joins.

Basically, you set a function to catch the posts_clauses (or one of the more restrictive query filters; but let's just use this for now), and then within that JOIN to the post_meta table for the fields you need, then order by them.

The important thing is to set up something to then remove the query filter. You could do it at the end of the function that catches it, but I have it separate in my plugin, so I'm going to stick with that. The 'wp' event works fine here.

add_filter('posts_clauses', 'filterMarkets')
add_filter('wp', 'removeQueryFilter');

public static function removeQueryFilter() {
  remove_filter('posts_clauses', 'filterMarkets');
}

public static function filterMarkets($clauses, $wp_query) {
global $wpdb;

$clauses['join'] .=<<<SQL
JOIN {$wpdb->postmeta} AS title_meta ON title_meta.post_id = {$wpdb->posts}.ID AND
title_meta.meta_key = 'fest_sortby'
JOIN {$wpdb->postmeta} AS start_date_meta ON start_date_meta.post_id = {$wpdb->posts}.ID
AND start_date_meta.meta_key = 'fest_market_start_date'
SQL;

  $clauses['orderby'] .= " title_meta.meta_value ASC";
  $clauses['orderby'] .= " start_date_meta.meta_value ASC";
}
return $clauses;

}

Answer 2

How about creating two custom fields:

rating_type = 'stars' or 'hearts'
rating_score = 1-5

then you can order by one custom field :

$args=array(
'posts_per_page' => $con_review_num,
'post_type' => 'con_product_reviews',
'paged' => $paged,
'orderby' => 'meta_value',
'order' => 'DESC',
'meta_key' => 'rating_score'   
);

and to know what kind of rating to show just use:

$rating_type = get_post_meta($post->ID,'rating_type',true);
if ($rating_type == "stars"){
   //show star rating
}else{
   //show heartsrating
}

Answer 3

Here is how I accomplished this using only one custom field. Instead of using "Stars" or "Hearts" as the custom field name and "1", "2", "3", etc. as the value, I used "Rating" as the name and "1 star", "2 stars", "3 stars", etc. (or hearts instead of stars) as the value. Since the unit of measurement is 0 thru 5 for both hearts and stars, I can sort by just the one custom field value of Rating and then parse out the value for display. Here is my code:

<?php 

// setup the query
$args='&posts_per_page='.$con_review_num.'&post_type=con_movie_reviews&paged='.$paged.'&order=DESC&orderby='.$feedsort.'&meta_key=Rating';

$review_loop = new WP_Query($args);

if ($review_loop->have_posts()) : while ($review_loop->have_posts()) : $review_loop->the_post(); $postcount++;

    // get the custom field value
    $rating = get_post_meta($post->ID, "Rating", $single = true);

    $unit = "star"; //default unit is star          
    if(strpos($rating," Hearts") || strpos($rating," hearts") || strpos($rating," Heart") || strpos($rating," heart")) {
    $unit = "heart"; //find out if we're using hearts instead of stars
    }
    // extract just the number from the rating meta key
    $rating = trim(str_replace(" star","",str_replace(" stars","",str_replace(" Star","",str_replace(" Stars","",str_replace(" heart","",str_replace(" hearts","",str_replace(" Heart","",str_replace(" Hearts","",$rating)))))))));
    if($stars) { // are we using stars or hearts?
        $rating = $stars;
        $unit = "star";
    } elseif($hearts) {
        $rating = $hearts;
        $unit = "heart";
    }   
    ?>

    <div class="stars tooltip" title="<?php echo $rating; ?> / 5 <?php echo $overall; ?>">

        <div class="<?php echo $unit; ?><?php if($rating>=1) { ?> full<?php } elseif($rating>0) { ?> half<?php } ?>">&nbsp;</div>

        <div class="<?php echo $unit; ?><?php if($rating>=2) { ?> full<?php } elseif($rating>1) { ?> half<?php } ?>">&nbsp;</div>

        <div class="<?php echo $unit; ?><?php if($rating>=3) { ?> full<?php } elseif($rating>2) { ?> half<?php } ?>">&nbsp;</div>

        <div class="<?php echo $unit; ?><?php if($rating>=4) { ?> full<?php } elseif($rating>3) { ?> half<?php } ?>">&nbsp;</div>

        <div class="<?php echo $unit; ?><?php if($rating>=5) { ?> full<?php } elseif($rating>4) { ?> half<?php } ?>">&nbsp;</div>

    </div>

    <?php 

}
endwhile;
endif;                  

?>