Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

Reading through the order & orderby documentation, it's not clear to me whether there is any support to order based on multiple meta_key values.

Obviously, using meta_query I can return posts based on multiple key-value pairs, but I want to be able to control the order that these results are returned based on multiple meta_keys.

For example, I have pages that have multiple categories and each category has a numerical rank. If a user is searching for pages that are in one of three different categories, I can return all the necessary posts with the following:

$query = array(
  'order' => 'DESC',
  'orderby' => 'meta_value_num',
  'meta_query' => array(
    'relation' => 'OR',
    array( 'key' => 'cat1', 'type' => 'numeric' ),
    array( 'key' => 'cat2', 'type' => 'numeric' ),
    array( 'key' => 'cat3', 'type' => 'numeric' )
  );
);

However, once those are returned, I would like them to be ordered based on the highest numerical value across any one of the categories that results were returned on. In other words, posts with a 9 value in cat1 would appear around the same order as posts with a 9 value in cat3.

Looking at this answer it seems that a meta_key isn't even necessary for 'orderby' => 'meta_value_num', but that doesn't match the documentation for meta_value, which is far more documented than meta_value_num... Any clarification would be useful. Thanks!

share|improve this question

1 Answer 1

If you look at the get_posts method of WP_Query it's easy to debug the sql by outputting the variable passed to the posts_request filter.

// Debug the WP_Query sql
add_filter( 'posts_request', function( $sql ) {
  echo "<pre>";
  var_dump( $sql );
  echo "</pre>";
  return $sql;
} );

The result should look something like this:

string(426) "SELECT SQL_CALC_FOUND_ROWS  wp_posts.* FROM wp_posts  INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) WHERE 1=1  AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') AND (
  wp_postmeta.meta_key = 'cat1'
  OR
  wp_postmeta.meta_key = 'cat2'
  OR
  wp_postmeta.meta_key = 'cat3'
) GROUP BY wp_posts.ID ORDER BY wp_postmeta.meta_value+0 DESC LIMIT 0, 10"

The query might look correct at first, but it's not. It uses the meta_value from the first row in the inner join for the ORDER BY clause. In this case that is 'cat1'. To change this behaviour we can use the posts_orderby hook to alter the ORDER BY statement and use the MAX mysql function so that it will use the highest value of all the meta_values instead of just the first row.

// Filter to alter the orderby sql
function nvdGMCWh_alter_posts_orderby( $sql ) {
  return "MAX( $wpdb->postmeta.meta_value+0 ) DESC";
} // - alter_posts_orderby
// Register the filter
add_filter( 'posts_orderby', 'nvdGMCWh_alter_posts_orderby' );

// The WP_Query
$res = new WP_Query;
$args = [
  'order' => 'DESC',
  'orderby' => 'meta_value_num',
  'meta_query' => [
    'relation' => 'OR',
    ['key' => 'cat1', 'type' => 'numeric'],
    ['key' => 'cat2', 'type' => 'numeric'],
    ['key' => 'cat3', 'type' => 'numeric'],
  ],
]; // - $args
// Execute the query and get the results
$result = $res->query( $args );
// Clean up the filter so it won't affect later queries
remove_filter( 'posts_orderby', 'nvdGMCWh_alter_posts_orderby' );
// Loop through the results
foreach ( $result as $post ) {
  // ... code here
} // - foreach
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.