[wp-trac] [WordPress Trac] #27344: Array support in WP_Meta_Query when compare operator is LIKE or NOT LIKE

[WordPress Trac]

#27344: Array support in WP_Meta_Query when compare operator is LIKE or NOT LIKE
-------------------------------------------------+-------------------------
 Reporter:  digitty                              |       Owner:
     Type:  enhancement                          |      Status:  new
 Priority:  normal                               |   Milestone:  Future
Component:  Query                                |  Release
 Severity:  normal                               |     Version:  3.8
 Keywords:  has-patch needs-refresh needs-unit-  |  Resolution:
  tests close                                    |     Focuses:
-------------------------------------------------+-------------------------
Changes (by boonebgorges):

 * keywords:  meta-query has-patch needs-refresh needs-unit-tests => has-
     patch needs-refresh needs-unit-tests close


Comment:

 digitty - Thanks for the idea. This is an interesting suggestion.

 I'm going to suggest that we don't make this change to the syntax of
 `WP_Meta_Query`. The main reason is that, as the conversation here
 suggests, simply allowing an array `value` for LIKE is ambiguous between
 AND and OR (and IN/NOT IN, for that matter).

 Introducing a new layer of syntax - like `'value' => array( 'AND' =>
 array( 'bob', 'rob' ) )` or another layer of 'relation' - doesn't seem
 worth it when you consider that tt's already possible to string together
 multiple LIKE/NOT LIKE clauses when building a meta_query argument. Your
 'OR' example can be translated to:

 {{{
 'meta_query' => array(
     'relation' => 'OR',
     array(
         'key' => 'cf_company_name',
         'value' => 'com',
         'compare' => 'LIKE',
     ),
     array(
         'key' => 'cf_company_name',
         'value' => 'net',
         'compare' => 'LIKE',
     ),
 )
 }}}

 And since [29887], it's possible to group these however you'd like, so
 that you're not limited in terms of how you want to combine these grouped
 LIKE queries with other query clauses. True, it's a bit more verbose than
 what you've suggested. But it keeps the syntax much simpler, and avoids
 our having more than one syntactical convention for expressing the same
 concept.

 This is sort of a side note, but your example for AND -
 `(wp_postmeta.meta_key = 'cf_contact_name' AND (wp_postmeta.meta_value
 LIKE '%bob%' AND wp_postmeta.meta_value LIKE '%rob%'))` - will only match
 rows that have both '%bob%' AND '%rob%'. To get separate rows, you need a
 table join. So this is a real edge case, and should probably be handled by
 translating it into: `LIKE '%bob%rob%' OR LIKE '%rob%bob%'`, which can be
 expressed in the syntax described above.

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