Sorting on meta_key breaks meta query with 'OR' relation
|Reported by:||Tomauger||Owned by:|
"Breaks" may have been a harsh term here. Things are working as they are supposed to, but there is room for improvement:
$movies = new WP_Query( array( 'meta_query' => array ( 'relation' => 'OR', array( 'key' => 'Year', 'value' => 2000, 'type' => 'numeric', 'compare' => '>=' ), array( 'key' => 'Rating', 'value' => 10, 'type' => 'numeric', 'compare' => '=' ) ), 'meta_key' => 'Rating', 'orderby' => 'meta_value_num', 'posts_per_page' => -1 ) );
Here, the desired outcome is to show movies from 2000 onward, but to also include any other movies that have a rating of '10'.
We currently need to specify the meta_key again, outside the meta_query in order to get the 'orderby' to work. Unfortunately this has the result of blowing away the meta query AND it seems to also not respect the order_by.
Here's the generated SQL:
SELECT wp_posts.* FROM wp_posts INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id) INNER JOIN wp_postmeta AS mt2 ON (wp_posts.ID = mt2.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 = 'Rating' OR (mt1.meta_key = 'Year' AND CAST(mt1.meta_value AS SIGNED) >= '2000') OR (mt2.meta_key = 'Rating' AND CAST(mt2.meta_value AS SIGNED) = '10') ) GROUP BY wp_posts.ID ORDER BY wp_postmeta.meta_value+0 DESC
Note the wp_postmeta.meta_key = "Rating" OR - is what's causing most of the ruckus (though the sort is still broken too, wondering about the +0, though at first glance that appears to be OK).
This is probably a duplicate, but I can't seem to find the original thread about sorting and post meta.