Search ONLY by meta key / meta values

I’m almost there on this one.

On functions.php i have this:

Read More
function base_home_product_post_type( $query ) {

    if($query->is_search() && $_POST['box'] == 'sku') {
        $query->query_vars['post_type'] = 'product';
        $query->query_vars['meta_key'] = 'sku';
        $query->query_vars['meta_value'] = $query->query_vars['s'];
        return;
    }

}
add_action('pre_get_posts', 'base_home_product_post_type', 1);

And it does what it’s told. Searches for the search string on the meta_key ‘sku’. Problem here is that by default, the query object returned from WordPress also contains this and queries post_title and post_content by default:

SELECT SQL_CALC_FOUND_ROWS  whirl_2_posts.ID FROM whirl_2_posts  INNER JOIN whirl_2_postmeta ON (whirl_2_posts.ID = whirl_2_postmeta.post_id) WHERE 1=1  AND (((whirl_2_posts.post_title LIKE '%__SEARCH_STRING__%')  OR (whirl_2_posts.post_content LIKE '%__SEARCH_STRING__%')))

I really don’t want that when i’m searching only for the “sku” value, so I’ve tried removing it using posts_where and some pretty ugly REGEX like this (warning, hacky and buggy):

function get_rid_of_titles($search) {
    if(is_search() && $_POST['box'] == 'sku') {
    $search = preg_filter('|ANDs({3}w*.post_titlesLIKEs'%[^%]*%') OR (w*.post_contentsLIKEs'%[^%]*%'){3}s|', '', $search);
    }
    return $search;
}
add_filter('posts_where','get_rid_of_titles');

But to no luck. Can anyone point me the how-to’s to search onlu for the custom value in this case?

SOLUTION

Thanks to @kaiser below, I got this working:

function get_rid_of_titles($search) {

    if(is_search() && $_POST['box'] == 'sku') {

          $sku = $_POST['s'];          // get search string
          $needle = "LIKE '%$sku%'";   // search only for LIKE '%_SEARCH_STRING_%'. This is a multisite environment, so involving table names directly is a bad idea
          $replace = "LIKE '%'";       // replace it with SQL Wildcard so any title and any content are welcome
          $count = 2;                  // only the first 2 occurrencies which happen to be post_title and post_content
          return str_replace($needle,$replace,$search, $count);

    }
}
add_filter('posts_where','get_rid_of_titles');

Related posts

Leave a Reply

1 comment

  1. You could do two things:

    A) replace the search with a SQL query that only searches the postmeta table (faster as there’s no JOIN involved – you can get the post via it’s ID later on, when displaying the results).

    B) Split the string right after WHERE 1=1 and drop the later part. You can use a simple return str_replace( $query_part_below, '', $search );.

     AND (
        (whirl_2_posts.post_title LIKE '%__SEARCH_STRING__%')  
        OR (whirl_2_posts.post_content LIKE '%__SEARCH_STRING__%')
    )
    

    Make sure that you properly $wpdb->prepare() before pushing it back in.