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!