[wp-trac] [WordPress Trac] #42546: WP_Query not handling 'LIKE' correctly.

[WordPress Trac]

#42546: WP_Query not handling 'LIKE' correctly.
--------------------------+-----------------------------
 Reporter:  tberneman     |      Owner:
     Type:  defect (bug)  |     Status:  new
 Priority:  normal        |  Milestone:  Awaiting Review
Component:  Query         |    Version:  4.8.3
 Severity:  normal        |   Keywords:
  Focuses:                |
--------------------------+-----------------------------
 I am doing custom PHP to retrieve listings (as a CPT) from the database
 for a Realty Company website I'm working on and one of the criteria is
 searching on the city from a search field on a form. The problem is the
 generated SQL has some sort of guid in it instead of the expected percent
 character normally used with LIKE. Here is the generated SQL from
 WP_Query:

 {{{
 SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_postmeta
 ON ( wp_posts.ID = wp_postmeta.post_id ) INNER JOIN wp_postmeta AS mt1 ON
 ( wp_posts.ID = mt1.post_id ) WHERE 1=1 AND ( ( wp_postmeta.meta_key =
 'city' AND wp_postmeta.meta_value LIKE
 '{f270e6c7d3b231242ceefa28fdd47243cac2100fe4fe8c9df633ec6f3cc2f583}fairfield{f270e6c7d3b231242ceefa28fdd47243cac2100fe4fe8c9df633ec6f3cc2f583}'
 ) AND ( mt1.meta_key = 'beds' AND mt1.meta_value >= '0' ) ) AND
 wp_posts.post_type = 'listings' AND (wp_posts.post_status = 'publish' OR
 wp_posts.post_status = 'future' OR wp_posts.post_status = 'draft' OR
 wp_posts.post_status = 'pending' OR wp_posts.post_status = 'private')
 GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 10
 }}}

 As you can see the generated code for searching the "city" field is:

 {{{
 wp_postmeta.meta_value LIKE
 '{f270e6c7d3b231242ceefa28fdd47243cac2100fe4fe8c9df633ec6f3cc2f583}fairfield{f270e6c7d3b231242ceefa28fdd47243cac2100fe4fe8c9df633ec6f3cc2f583}'
 }}}

 The "expected" code should look like this:

 {{{
 wp_postmeta.meta_value LIKE '%fairfield%'
 }}}

 The form is found here: https://millersells.com/search-2/

 After making selections and clicking the Search button it calls itself
 with url parameters and you get one of these errors:
 "Fatal error: Maximum execution time of 60 seconds exceeded in
 /home/extremx1/public_html/millersells2017b/wp-includes/query.php on line
 0"
 OR
 "Fatal error: Maximum execution time of 60 seconds exceeded in
 /home/extremx1/public_html/millersells2017b/wp-includes/class-wp-query.php
 on line 3090"

 (Example URL with parameters:
 https://millersells.com/search-2/?cf_id=37&loc=fairfield&type=SingleFamilyHome&beds=3&baths=2&min_price=0&max_price=0)

 I am using this PHP code to create the query (for now I'm just working
 with 2 of the search fields):
 {{{
 <?php

 if (isset($_GET['loc'])) $location = trim($_GET['loc']); else $location =
 '';
 if (isset($_GET['type'])) $type = trim($_GET['type']); else $type = '';
 if (isset($_GET['beds'])) $bedrooms = trim($_GET['beds']); else $bedrooms
 = 0;
 if (isset($_GET['baths'])) $bathrooms = trim($_GET['baths']); else
 $bathrooms = 0;
 if (isset($_GET['min_price'])) $min_price = trim($_GET['min_price']); else
 $min_price = 0;
 if (isset($_GET['max_price'])) $max_price = trim($_GET['max_price']); else
 $max_price = 999999999;
 $location = 'fairfield';

 $args = array(
         'post_type' => array( 'listings' ),
         'meta_query' => array(
                 array(
                         'key' => 'city',
                         'value' => $location,
                         'type' => 'CHAR',
                         'compare' => 'LIKE',
                 ),
                 array(
                         'key' => 'beds',
                         'value' => $bedrooms,
                         'compare' => '>='
                 )
         )
 );

 $the_query = new WP_Query( $args );

 ?>
 }}}

 To see the generated SQL I added this code at line 2752 in wp_includes
 /class-wp-query.php: echo $this->request;

 Also to aid in debugging I would suggest you add a "generated_sql"
 parameter to the query object that we can echo to see what the actual
 generated SQL looks like. EX: {{{echo $the_query->generated_sql;}}}

 Thanks!

 Tim :o]

 PS: Your welcome to contact me at tberneman at gmail.com

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