[wp-trac] [WordPress Trac] #56802: Query: Post IDs cached for search and other 'LIKE' queries are unreachable

[WordPress Trac]

#56802: Query: Post IDs cached for search and other 'LIKE' queries are unreachable
-------------------------------------------------+-------------------------
 Reporter:  dlh                                  |       Owner:
                                                 |  peterwilsoncc
     Type:  defect (bug)                         |      Status:  reopened
 Priority:  high                                 |   Milestone:  6.1
Component:  Query                                |     Version:  6.1
 Severity:  normal                               |  Resolution:
 Keywords:  has-patch has-unit-tests needs-      |     Focuses:
  testing fixed-major                            |  performance
-------------------------------------------------+-------------------------

Comment (by AlanP57):

 In some cases, I prefer not to use WP_Query when display data. This is the
 code from the plugin:

 {{{#!php
 <?php
       $sql = $wpdb->prepare("(select $wpdb->posts.ID, post_title,
 {$wpdb->prefix}mgmlp_folders.folder_id, pm.meta_value as attached_file,
 'a' as item_type
 from $wpdb->posts
 LEFT JOIN {$wpdb->prefix}mgmlp_folders ON($wpdb->posts.ID =
 {$wpdb->prefix}mgmlp_folders.post_id)
 LEFT JOIN $wpdb->postmeta AS pm ON (pm.post_id = $wpdb->posts.ID)
 LEFT JOIN $wpdb->users AS us ON ($wpdb->posts.post_author = us.ID)
 where post_type = 'mgmlp_media_folder' and pm.meta_key =
 '_wp_attached_file' and SUBSTRING_INDEX(pm.meta_value, '/', -1) like
 '%%%s%%')
 union all
 (select $wpdb->posts.ID, post_title,
 {$wpdb->prefix}mgmlp_folders.folder_id, pm.meta_value as attached_file,
 'b' as item_type
 from $wpdb->posts
 LEFT JOIN {$wpdb->prefix}mgmlp_folders ON( $wpdb->posts.ID =
 {$wpdb->prefix}mgmlp_folders.post_id)
 LEFT JOIN $wpdb->postmeta AS pm ON (pm.post_id = $wpdb->posts.ID)
 LEFT JOIN $wpdb->users AS us ON ($wpdb->posts.post_author = us.ID)
 where post_type = 'attachment' and pm.meta_key = '_wp_attached_file' and
 SUBSTRING_INDEX(pm.meta_value, '/', -1) like '%%%s%%') order by
 attached_file", $search_string, $search_string);

 }}}

 And here is an example of the SQL:
 (select wp_posts.ID, post_title, wp_mgmlp_folders.folder_id, pm.meta_value
 as attached_file, 'a' as item_type
 from wp_posts
 LEFT JOIN wp_mgmlp_folders ON(wp_posts.ID = wp_mgmlp_folders.post_id)
 LEFT JOIN wp_postmeta AS pm ON (pm.post_id = wp_posts.ID)
 LEFT JOIN wp_users AS us ON (wp_posts.post_author = us.ID)
 where post_type = 'mgmlp_media_folder' and pm.meta_key =
 '_wp_attached_file' and SUBSTRING_INDEX(pm.meta_value, '/', -1) like
 '{48bf6209debff2ee81208ffaee83c0ccfe32af6953d915a72a2fd46f1d0be2e1}'my
 search
 text'{48bf6209debff2ee81208ffaee83c0ccfe32af6953d915a72a2fd46f1d0be2e1}')
 union all
 (select wp_posts.ID, post_title, wp_mgmlp_folders.folder_id, pm.meta_value
 as attached_file, 'b' as item_type
 from wp_posts
 LEFT JOIN wp_mgmlp_folders ON( wp_posts.ID = wp_mgmlp_folders.post_id)
 LEFT JOIN wp_postmeta AS pm ON (pm.post_id = wp_posts.ID)
 LEFT JOIN wp_users AS us ON (wp_posts.post_author = us.ID)
 where post_type = 'attachment' and pm.meta_key = '_wp_attached_file' and
 SUBSTRING_INDEX(pm.meta_value, '/', -1) like
 '{48bf6209debff2ee81208ffaee83c0ccfe32af6953d915a72a2fd46f1d0be2e1}'my
 search
 text'{48bf6209debff2ee81208ffaee83c0ccfe32af6953d915a72a2fd46f1d0be2e1}')
 order by attached_file

 This use to work in versions before 6.1.

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