[wp-trac] [WordPress Trac] #53450: [WP_Meta_Query] Add faster LIKE based 'STARTSWITH' and 'ENDSWITH' compare modes for value query

[WordPress Trac]

#53450: [WP_Meta_Query] Add faster LIKE based 'STARTSWITH' and 'ENDSWITH' compare
modes for value query
-------------------------------------------------+-------------------------
 Reporter:  janthiel                             |       Owner:  (none)
     Type:  enhancement                          |      Status:  new
 Priority:  normal                               |   Milestone:  Awaiting
                                                 |  Review
Component:  Query                                |     Version:  5.7.2
 Severity:  normal                               |  Resolution:
 Keywords:  dev-feedback has-patch needs-docs    |     Focuses:
  needs-codex has-unit-tests                     |  performance
-------------------------------------------------+-------------------------

Comment (by boonebgorges):

 @jorbin Thanks for the ping, and @janthiel thanks for the ticket.

 I agree strongly with the motivation for this ticket. `REGEXP` should be
 avoided where possible, and it's likely that many of the real-world uses
 of `REGEXP` would be covered by the suggested new feature.

 My one misgiving is about the syntax. Currently, all values of `compare`
 are MySQL keywords. `STARTSWITH` etc, being passed to `compare`
 (especially in all upper-case), give the false appearance of being
 keywords. I'm concerned that this could cause confusion.

 Here's an idea for an alternative syntax. A number of years ago,
 BuddyPress introduced a similar feature, but added a separate parameter to
 trigger it: `search_wildcard`, which defaults to `'both'` but accepts
 `'right'` or `'left'`. See
 https://buddypress.trac.wordpress.org/ticket/5769

 In the case of meta queries, this might look like this:

 {{{#!php
 [
   'key'                    => 'foo',
   'value'                  => 'bar'
   'compare'                => 'LIKE',
   'like_wildcard_position' => 'left', // defaults to 'both'
 ]
 }}}

 We might find a better name for the parameter than this, and we might also
 consider 'start' and 'end' instead of 'left' and 'right'.

 In any case, I think that this kind of syntax is better for internal
 consistency. Does anyone have thoughts about whether this is a better or
 worse idea?

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