WordPress query: merge meta key (number) values and sort

Question

There's a lot of great documentation on multiple column sorting using orderby however I can't seem to find a good WP_Query solution to this problem.

Each post has two meta keys: rating_au and rating_overall.

I want to query posts by rating_au as a priority and then falling back to rating_overall if no value for rating_au exists.

Example Table

| post ID | rating_au | rating_overall |
|---------|-----------|----------------|
| 1       |           | 80             |
| 2       | 90        | 75             |
| 3       |           | 70             |

Example Output

• 2
• 1
• 3

I have got as far as the following query:

$args = [
  'posts_per_page' => 5,
  'post_type' => 'post',
  'meta_query' => [
    'relation' => 'OR',
    'rating_au_clause' => [
      'key' => 'rating_au',
      'compare' => 'EXISTS'
    ],
    'rating_overall_clause' => [
      'key' => 'rating_overall',
      'compare' => 'EXISTS'
    ]
  ],
  'orderby' => 'rating_au_clause rating_overall_clause',
  'order' => 'DESC'
];

I think I'm probably misunderstanding how this translates to MySQL but it does not produce the desired outcome (it simply lists descending order for rating_overall).

I'm wracking my brains on this and am looking to avoid $wpdb or running multiple queries and merging values.

It's likely there's an entirely different approach to this problem. Any suggestions are gratefully received!

Answer 1

I think this is the syntax you are after:

$args = [
  'posts_per_page' => 5,
  'post_type' => 'post',
  'meta_query' => [
    'relation' => 'OR',
    'rating_au_clause' => [
      'key' => 'rating_au',
      'compare' => 'EXISTS'
    ],
    'rating_overall_clause' => [
      'key' => 'rating_overall',
      'compare' => 'EXISTS'
    ]
  ],
  'orderby' => [ 
        'rating_au_clause' => 'ASC',
        'rating_overall_clause' => 'DESC',
    ],
];

It's a subtle difference, but uses your named meta queries in an orderby array.