0

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!

1 Answer 1

0

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.

1
  • I believe this is the same query just structured differently and using ASC for rating_au_clause. Unfortunately this doesn't achieve the desired result mentioned above.
    – Nick
    Apr 30, 2020 at 12:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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