[wp-trac] [WordPress Trac] #21617: Sorting on meta_key breaks meta query with 'OR' relation

[WordPress Trac]

#21617: Sorting on meta_key breaks meta query with 'OR' relation
--------------------------+-----------------------------
 Reporter:  Tomauger      |      Owner:
     Type:  defect (bug)  |     Status:  new
 Priority:  normal        |  Milestone:  Awaiting Review
Component:  General       |    Version:
 Severity:  normal        |   Keywords:
--------------------------+-----------------------------
 "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.

-- 
Ticket URL: <http://core.trac.wordpress.org/ticket/21617>
WordPress Trac <http://core.trac.wordpress.org/>
WordPress blogging software