[wp-trac] [WordPress Trac] #29642: Support complex (nested, multiple relation) queries in WP_Meta_Query

[WordPress Trac]

#29642: Support complex (nested, multiple relation) queries in WP_Meta_Query
--------------------------------+-----------------------------
 Reporter:  boonebgorges        |      Owner:
     Type:  enhancement         |     Status:  new
 Priority:  normal              |  Milestone:  Awaiting Review
Component:  Options, Meta APIs  |    Version:
 Severity:  normal              |   Keywords:
  Focuses:                      |
--------------------------------+-----------------------------
 The syntax of `WP_Meta_Query` currently limits the kind of queries you can
 use it for. It's only possible to use a single 'relation', which means
 that you can do a string of clauses connected by OR or AND. But you can't
 group these clauses together and use a mix of relations, which makes it
 impossible to do something like, say:

 {{{
 SELECT posts WHERE (
     ( foo = bar AND foo1 = bar )
     OR
     ( foo = baz AND foo1 = baz )
     OR
     foo = barry
 )
 }}}

 I propose a restructuring of WP_Meta_Query so that it accepts arbitrary
 nested queries. For example, the pseudocode above would be represented as
 a meta_query argument that looks like this:

 {{{
 array(
     'relation' => 'OR',
     array(
         'relation' => 'AND',
         array(
             'key' => 'foo',
             'value' => 'bar',
         ),
         array(
             'key' => 'foo1',
             'value' => 'bar',
         ),
     ),
     array(
         'relation' => 'AND',
         array(
             'key' => 'foo',
             'value' => 'baz',
         ),
         array(
             'key' => 'foo1',
             'value' => 'baz',
         ),
     ),
     array(
         'key' => 'foo',
         'value' => 'barry',
     ),
 ),
 }}}

 The attached patch is a first pass at this restructuring. A few notes:

 - Syntax for current queries is unchanged, as is the SQL that's generated.
 This means that backward compatibility should be maintained in all cases
 (though see below for some very edge-case questions). I've also included
 all optimizations that have been baked into WP_Meta_Query over the years
 (such as #19729).

 - The attached patch contains simple unit tests for the new functionality.
 All existing unit tests are also passing. The extended unit test suite for
 WP_Meta_Query is also fully passing (#29560), with the exception of a
 couple very minor syntax quibbles that should not reflect any changes in
 functionality.

 - One fairly significant change in the internal SQL-building logic is that
 I've broken EXISTS queries out into their own block. Currently, EXISTS
 falls back on keyless '=' queries in a pretty opaque way; my change does
 not change the SQL that results, but I think it makes the code much
 clearer.

 - I have changed some of the codestyling of the SQL generated by the
 class. I've normalized the indentation so that the nesting is clear when
 debugging, eg

 {{{
 WHERE (
     ( meta_key = 'foo' AND meta_value = 'bar' )
     OR
     ( meta_key = 'foo1' AND meta_value = 'bar' )
 )
 }}}

 I also normalized the spaces around the parentheses in the SQL being
 generated. It's possible that these minor codestyling changes will affect
 plugins that are performing certain kinds of string operations on the
 'get_meta_sql' filter (think - `explode( "\n", $sql['where'] )` ). I can
 roll back these formatting changes to eliminate the possibility of issues
 with these sorts of plugins, but I figured that the increase in usability
 might be worth the tradeoff in this case.

 See #20312 for a related request that was closed as wontfix.

 Feedback welcome.

--
Ticket URL: <https://core.trac.wordpress.org/ticket/29642>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform