Is it possible to orderby multiple meta_keys when using meta_value_num?

4 Views

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.

Read More

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!

Post Views: 4

Related posts

Leave a Reply

2 comments

  1. You might want to check out the query improvements in WP 4.2 for ‘orderby’ and ‘meta_query’. Details are on https://make.wordpress.org/core/2015/03/30/query-improvements-in-wp-4-2-orderby-and-meta_query.

    Try to name your meta_query clauses, then order by them.

    Following code is untested:

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

  2. 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