[wp-trac] [WordPress Trac] #42400: WP_User_Query is broken in 4.8.3

[WordPress Trac]

#42400: WP_User_Query is broken in 4.8.3
--------------------------+-----------------------------
 Reporter:  amityweb      |      Owner:
     Type:  defect (bug)  |     Status:  new
 Priority:  normal        |  Milestone:  Awaiting Review
Component:  Query         |    Version:
 Severity:  normal        |   Keywords:
  Focuses:                |
--------------------------+-----------------------------
 We have a custom search and results function using WP_User_Query. It was
 working fine up to 4.8.2. Upgrading to 4.8.3 has resulted in no results in
 the search but it should return results.

 Upon further investigation, the SQL generated in 4.8.3 is no longer
 correct, in fact it contains some strange strings instead of the % for a
 LIKE. It contains the following string instead of a %:
 '''822ca44cdf3a7a6a970c2de8f6572380778bc20bd09bc0858b11cea8b4230d96'''

 In 4.8.2 the SQL generated from our WP_User_Query( $args ) was as follows:


 {{{
 SELECT DISTINCT SQL_CALC_FOUND_ROWS wp_users.* FROM wp_users LEFT JOIN
 wp_usermeta ON ( wp_users.ID = wp_usermeta.user_id )  LEFT JOIN
 wp_usermeta AS mt1 ON ( wp_users.ID = mt1.user_id )  LEFT JOIN wp_usermeta
 AS mt2 ON (wp_users.ID = mt2.user_id AND mt2.meta_key =
 'show_in_directory' )  LEFT JOIN wp_usermeta AS mt3 ON ( wp_users.ID =
 mt3.user_id )  LEFT JOIN wp_usermeta AS mt4 ON (wp_users.ID = mt4.user_id
 AND mt4.meta_key = 'show_member_in_directory' ) WHERE 1=1 AND (
   (
     ( wp_usermeta.meta_key LIKE 'subject_specialism_%_subject' AND
 wp_usermeta.meta_value LIKE '%Economics%' )
     AND
     (
       ( mt1.meta_key = 'show_in_directory' AND mt1.meta_value = 'Yes' )
       OR
       mt2.user_id IS NULL
     )
     AND
     (
       ( mt3.meta_key = 'show_member_in_directory' AND mt3.meta_value =
 'Yes' )
       OR
       mt4.user_id IS NULL
     )
   )
 ) ORDER BY RAND()
 }}}



 In 4.8.3 the SQL generated is as follows, and so our custom search
 function is no longer returning results:




 {{{
 SELECT DISTINCT SQL_CALC_FOUND_ROWS wp_users.* FROM wp_users LEFT JOIN
 wp_usermeta ON ( wp_users.ID = wp_usermeta.user_id )  LEFT JOIN
 wp_usermeta AS mt1 ON ( wp_users.ID = mt1.user_id )  LEFT JOIN wp_usermeta
 AS mt2 ON (wp_users.ID = mt2.user_id AND mt2.meta_key =
 'show_in_directory' )  LEFT JOIN wp_usermeta AS mt3 ON ( wp_users.ID =
 mt3.user_id )  LEFT JOIN wp_usermeta AS mt4 ON (wp_users.ID = mt4.user_id
 AND mt4.meta_key = 'show_member_in_directory' ) WHERE 1=1 AND (
   (
     ( wp_usermeta.meta_key LIKE
 'subject_specialism_{822ca44cdf3a7a6a970c2de8f6572380778bc20bd09bc0858b11cea8b4230d96}_subject'
 AND wp_usermeta.meta_value LIKE
 '{822ca44cdf3a7a6a970c2de8f6572380778bc20bd09bc0858b11cea8b4230d96}Antiques{822ca44cdf3a7a6a970c2de8f6572380778bc20bd09bc0858b11cea8b4230d96}'
 )
     AND
     (
       ( mt1.meta_key = 'show_in_directory' AND mt1.meta_value = 'Yes' )
       OR
       mt2.user_id IS NULL
     )
     AND
     (
       ( mt3.meta_key = 'show_member_in_directory' AND mt3.meta_value =
 'Yes' )
       OR
       mt4.user_id IS NULL
     )
   )
 ) ORDER BY RAND()
 }}}




 For reference this is our full search



 {{{
 // Get Search Terms
 if(isset($_GET['subject']) && $_GET['subject'] != '')
 {
         // Strip hyphen and numbers from end, just in case its a duplicate
         $search_term = preg_replace("/-\d+$/","",$_GET['subject']);
 }

 // Get thesaurus terms from $search_term
 $thesaurus = get_field('subject_thesaurus', 'option');
 foreach($thesaurus as $key => $value)
 {
         if ((strcasecmp($value['alternative_search_term'], $search_term)
 == 0))
         {
                 $search_term = $value['equivalent_search_term'];
                 break;
         }
 }


 // Get any URL variables for filters
 $search = array();
 $search_results = false;

 if(isset($search_term) && $search_term != '')
 {
         $search_results = true;
         $search[] = array(
                 'key'           => 'subject_specialism_%_subject',
                 'value'         => stripslashes($search_term),
                 'compare'       => 'LIKE',
         );
 }
 if(isset($_GET['publication_type']) && $_GET['publication_type'] != '')
 {
         $search_results = true;
         $search[] = array(
                 'key'           => 'publication_type_%_publication_type',
                 'value'         =>
 stripslashes($_GET['publication_type']),
                 'compare'       => 'LIKE',
         );
 }

 $search[] = array(
         'relation'              => 'OR',
         array(
                 'key'           => 'show_in_directory',
                 'value'         => 'Yes',
                 'compare'       => '=',
         ),
         array(
                 'key'           => 'show_in_directory',
                 'compare'       => 'NOT EXISTS',
         ),
 );

 $search[] = array(
         'relation'              => 'OR',
         array(
                 'key'           => 'show_member_in_directory', // Admin
 settings
                 'value'         => 'Yes',
                 'compare'       => '=',
         ),
         array(
                 'key'           => 'show_member_in_directory',
                 'compare'       => 'NOT EXISTS',
         ),
 );

 // Create args array for get_users from the Pro members IDs
 $args = array(
         'meta_query'    => array(
                 'relation'              => 'AND',
                 $search
         ),
         'include'      => $member_ids,
 );

 // Create random function because WP_User_Query does not have it
 if($search_results)
 {
         $randomize_func = create_function( '&$query',
 '$query->query_orderby = "ORDER BY RAND()";' );
         add_action( 'pre_user_query', $randomize_func );
 }

 $user_query = new WP_User_Query( $args );
 }}}

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