[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:  boonebgorges
     Type:  defect (bug)  |      Status:  accepted
 Priority:  normal        |   Milestone:  4.1
Component:  Query         |     Version:
 Severity:  major         |  Resolution:
 Keywords:                |     Focuses:
--------------------------+---------------------------
Changes (by boonebgorges):

 * keywords:  needs-patch =>
 * owner:  wonderboymusic => boonebgorges
 * status:  reopened => accepted
 * milestone:  Future Release => 4.1


Comment:

 The duplicate ticket #29604 prompted me to do some more investigation of
 this issue. I managed to confirm it, with the critical piece of the puzzle
 being that the bug only arises when you have more than one meta_query
 clause being linked by the 'OR' relation. This wasn't stated clearly in
 the original bug report, and the unit test that jackreichert originally
 suggested didn't set up the data in the right way to illustrate the
 problem.

 The crux of the issue is along the lines of what jackreichert discusses
 here https://core.trac.wordpress.org/ticket/25538#comment:3. It breaks
 down like this: 'orderby=meta_value&meta_key=foo' requires that each
 located item have a value for 'foo'. But when this is coupled with a
 meta_query whose relation is 'OR', the query will return results that
 *don't* have 'foo', as long as they meet one of the other OR conditions.
 Schematically, if you have 'orderby=meta_value&meta_key=foo' along with
 meta_query = (color = blue OR vegetable = celery), that should compile
 down to a nested query that links the orderby clause together with the
 rest of the meta query with an AND. In other words, the end result needs
 to be something like:

 {{{
 mt1.meta_key = 'foo' AND (
     ( mt2.meta_key = 'color' AND mt2.meta_value = 'blue' )
     OR
     ( mt3.meta_key = 'vegetable' AND mt3.meta_value = 'celery' )
 )
 }}}

 In other words, the syntax has to be nested. Part of the problem with the
 approaches previously ventured to solve this problem was that
 WP_Meta_Query didn't support the construction of this sort of nested
 syntax, but since #29642 [29887] it does. (Tada!) So now we can fix this
 problem properly.

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