[wp-trac] [WordPress Trac] #23373: Limiting get_users() results by meta value with LIKE wildcards

[WordPress Trac]

#23373: Limiting get_users() results by meta value with LIKE wildcards
-------------------------+------------------------------
 Reporter:  mpwalsh8     |       Owner:
     Type:  enhancement  |      Status:  new
 Priority:  normal       |   Milestone:  Awaiting Review
Component:  Users        |     Version:  3.5
 Severity:  normal       |  Resolution:
 Keywords:  has-patch    |
-------------------------+------------------------------
Changes (by SergeyBiryukov):

 * version:  trunk => 3.5


Old description:

> I want to limit the results returned by get_users() to a subset of the
> users based on some criteria that will be stored in the User Meta table
> (e.g. all users whose first name begins with M).
>
> Here arr the args I am passing to get_users():
> (
>     [include] =>
>     [exclude] =>
>     [fields] => all_with_meta
>     [meta_key] => last_name
>     [meta_value] => M%
>     [meta_compare] => LIKE
> )
>
> This is the query the WordPress constructs for me:
>
> [01-Feb-2013 16:01:08 UTC] WP_User_Query Object
> (
>     [query_vars] => Array
>         (
>             [blog_id] => 1
>             [role] =>
>             [meta_key] => last_name
>             [meta_value] => M%
>             [meta_compare] => LIKE
>             [include] =>
>             [exclude] =>
>             [search] =>
>             [search_columns] => Array
>                 (
>                 )
>
>             [orderby] => login
>             [order] => ASC
>             [offset] =>
>             [number] =>
>             [count_total] =>
>             [fields] => all_with_meta
>             [who] =>
>         )
>
>     [results] => Array
>         (
>         )
>
>     [total_users] => 0
>     [query_fields] => wp_users.ID
>     [query_from] => FROM wp_users INNER JOIN wp_usermeta ON (wp_users.ID
> = wp_usermeta.user_id)
>     [query_where] => WHERE 1=1 AND ( (wp_usermeta.meta_key = 'last_name'
> AND CAST(wp_usermeta.meta_value AS CHAR) LIKE '%M\\%%') )
>     [query_orderby] => ORDER BY user_login ASC
>     [query_limit] =>
>

> ----
>
> As you can see, the LIKE clause is escaped and wrapped with SQL wildcard
> operators.  I can't figure out how to prevent this from happening as what
> I want is "LIKE 'M%'" to be my actually query.
>
> ''
> Andrew Nacin wp at andrewnacin.com via lists.automattic.com
> 11:24 am (1 day ago) to wp-hackers
>
> Currently, "LIKE" prepends and appends wildcards, as in %term%. We could
> add "%LIKE" and "LIKE%", though that would not help with the situation
> where you want LIKE taken literally, potentially without % wildcards at
> all. (Such as using _ as a single-character wildcard, or looking for case
> insensitive matching.) Probably the best way to add to this API would be
> to allow for a 'like_escape' => false; that would accept your value as
> provided. If you'd like to submit a ticket (and patch), this can
> definitely find its way into 3.6.
>
> Nacin''
>

> ----
>
> I have prepared a patch to meta.php which allows escaped LIKE and NOT
> LIKE meta compares to work properly.

New description:

 I want to limit the results returned by get_users() to a subset of the
 users based on some criteria that will be stored in the User Meta table
 (e.g. all users whose first name begins with M).

 Here arr the args I am passing to get_users():
 {{{
 (
     [include] =>
     [exclude] =>
     [fields] => all_with_meta
     [meta_key] => last_name
     [meta_value] => M%
     [meta_compare] => LIKE
 )
 }}}
 This is the query the WordPress constructs for me:
 {{{
 [01-Feb-2013 16:01:08 UTC] WP_User_Query Object
 (
     [query_vars] => Array
         (
             [blog_id] => 1
             [role] =>
             [meta_key] => last_name
             [meta_value] => M%
             [meta_compare] => LIKE
             [include] =>
             [exclude] =>
             [search] =>
             [search_columns] => Array
                 (
                 )

             [orderby] => login
             [order] => ASC
             [offset] =>
             [number] =>
             [count_total] =>
             [fields] => all_with_meta
             [who] =>
         )

     [results] => Array
         (
         )

     [total_users] => 0
     [query_fields] => wp_users.ID
     [query_from] => FROM wp_users INNER JOIN wp_usermeta ON (wp_users.ID =
 wp_usermeta.user_id)
     [query_where] => WHERE 1=1 AND ( (wp_usermeta.meta_key = 'last_name'
 AND CAST(wp_usermeta.meta_value AS CHAR) LIKE '%M\\%%') )
     [query_orderby] => ORDER BY user_login ASC
     [query_limit] =>

 }}}
 ----

 As you can see, the LIKE clause is escaped and wrapped with SQL wildcard
 operators.  I can't figure out how to prevent this from happening as what
 I want is "LIKE 'M%'" to be my actually query.

  Andrew Nacin wp at andrewnacin.com via lists.automattic.com
  11:24 am (1 day ago) to wp-hackers

  Currently, "LIKE" prepends and appends wildcards, as in %term%. We could
 add "%LIKE" and "LIKE%", though that would not help with the situation
 where you want LIKE taken literally, potentially without % wildcards at
 all. (Such as using _ as a single-character wildcard, or looking for case
 insensitive matching.) Probably the best way to add to this API would be
 to allow for a 'like_escape' => false; that would accept your value as
 provided. If you'd like to submit a ticket (and patch), this can
 definitely find its way into 3.6.

  Nacin


 ----

 I have prepared a patch to meta.php which allows escaped LIKE and NOT LIKE
 meta compares to work properly.

--

-- 
Ticket URL: <http://core.trac.wordpress.org/ticket/23373#comment:1>
WordPress Trac <http://core.trac.wordpress.org/>
WordPress blogging software