[wp-trac] [WordPress Trac] #25538: WP_Query: OR relation breaks orderby meta_value

[WordPress Trac]

#25538: WP_Query: OR relation breaks orderby meta_value
------------------------------------------+------------------
 Reporter:  darrengrant                   |       Owner:
     Type:  defect (bug)                  |      Status:  new
 Priority:  normal                        |   Milestone:  3.9
Component:  Query                         |     Version:
 Severity:  major                         |  Resolution:
 Keywords:  needs-patch needs-unit-tests  |     Focuses:
------------------------------------------+------------------

Comment (by jackreichert):

 I figured out the problem. I'm net yet fluent with the inner workings of
 the WP_Query class, yet, to find where to fix the problem. I'll dig into
 that next, but I wanted to post my findings first so someone else can jump
 in if they know where.

 If you spit out both queries the first is:
 {{{
 SELECT SQL_CALC_FOUND_ROWS wp_posts.*
 FROM wp_posts
         INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.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 = 'order'
         AND CAST(wp_postmeta.meta_value AS CHAR) >= '1') )
 GROUP BY wp_posts.ID
 ORDER BY wp_postmeta.meta_value DESC
 LIMIT 0, 10
 }}}

 The second is:
 {{{
 SELECT SQL_CALC_FOUND_ROWS 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)
 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 = 'order'
                 AND CAST(wp_postmeta.meta_value AS CHAR) >= '1')
                 OR (mt1.meta_key = 'order'
                 AND CAST(mt1.meta_value AS CHAR) >= '1') )
 GROUP BY wp_posts.ID
 ORDER BY wp_postmeta.meta_value DESC
 LIMIT 0, 10
 }}}

 In order to see what's happening I changed the select from wp_posts.* to
 *.

 When you do this it becomes clear what is happening. For efficiency sake
 I'll only show wp_posts.ID, wp_postmeta.*,  mt1.*.

 Here are the results:
 [[Image(http://www.jackreichert.com/asset/d3dc35aa/8f2175db.png/)]]

 As you can see. The OR comes out in the wrong place, so when it running
 the condition, it finds mt1 where meta_key = 'order' and doesn't care what
 meta_key the first joined table joins on. So we get wp_postmeta.meta_key =
 '_edit_last' in this case.

 The solution is, instead of the query condition being:
 {{{
 AND ( (wp_postmeta.meta_key = 'order'
                 AND CAST(wp_postmeta.meta_value AS CHAR) >= '1')
                 OR (mt1.meta_key = 'order'
                 AND CAST(mt1.meta_value AS CHAR) >= '1') )
 }}}

 it should be:
 {{{
         AND wp_postmeta.meta_key = 'order'
         AND mt1.meta_key = 'order'
         AND (CAST(wp_postmeta.meta_value AS CHAR) >= '1'
                 OR CAST(mt1.meta_value AS CHAR) >= '1')
 }}}

 Pulling the meta_key comparison out of the OR, so the meta_key in both HAS
 to equal 'order'; the condition should ONLY be applied to the meta_value
 condition.

--
Ticket URL: <https://core.trac.wordpress.org/ticket/25538#comment:3>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform