Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

I want the native WordPress search input to search metadata in addition to its standard title and content search, however setting a meta_query adds an AND clause to the request when what I need is an OR.

My function:

function search_faqs_metadata($query) {
  if(!is_admin() && $query->is_main_query()) {
    if($query->is_search) {
      $query->set('meta_query', array(array(
        'key' => '_faqs',
        'value' => $query->query_vars['s'],
        'compare' => 'LIKE'
      )));
    }
  }
}
add_action('pre_get_posts', 'search_faqs_metadata');

The problem is the query it generates:

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts
INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
WHERE 1=1
AND (
  (
    (wp_posts.post_title LIKE '%foobar%') OR (wp_posts.post_content LIKE '%foobar%')
  )
)
AND (wp_posts.post_password = '')
AND wp_posts.post_type IN ('post', 'page', 'attachment')
AND (wp_posts.post_status = 'publish')
AND (  //I need this to be an OR!
  (wp_postmeta.meta_key = '_faqs' AND CAST(wp_postmeta.meta_value AS CHAR) LIKE '%foobar%')
)
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_title LIKE '%foobar%' DESC, wp_posts.post_date DESC LIMIT 0, 10

So I don't get a match unless the post title/content AND the metadata match the search term.

I understand there is a relation parameter for the meta_query but it only works when comparing multiple metadata values.

Currently I'm running str_replace() on the final query to change the last AND into an OR. I chose this method is because, while hacky, it is lean on the amount of code I need to add for this adjustment to succeed:

function search_where_or_metadata($where) {
  if(is_search()) {
    global $wp_query;
    $new_where = $where;
    $new_where = str_replace('AND ( (wp_postmeta.meta_key', 'OR ( (wp_postmeta.meta_key', $where);
    return $new_where;
  }
  return $where;
}
add_filter('posts_where', 'search_where_or_metadata');

EDIT Here is a better function using a more appropriate filter that changes the AND to OR:

function change_meta_key_where_from_and_to_or($sql) {
  if(is_search()) {
    $sql['where'] = substr($sql['where'], 1, 3) == 'AND' ? substr_replace($sql['where'], 'OR', 1, 3) : $sql['where'];
  }
  return $sql;
}
add_filter('get_meta_sql', 'change_meta_key_where_from_and_to_or');

Is there a better way to change that final AND into an OR?

share|improve this question
    
How are you using str_replace on the final query? I'd probably use preg_replace on posts_where, probably, but I am fairly sure that something similar to this is the only choice you have. –  s_ha_dum May 6 at 0:52
    
What hook are you using? –  s_ha_dum May 6 at 0:55
    
I edited the question to show the filter I'm using to replace the string currently. –  cfx May 6 at 0:58
    
Like I said, I'd use preg_replace but this looks like the right approach to me. –  s_ha_dum May 6 at 4:30
    
Updated my function above to use the get_meta_sql hook which is specific to meta queries –  cfx May 6 at 15:03

2 Answers 2

up vote 1 down vote accepted

I believe the shortest and best approach is to (1) modify the query:

function search_faqs_metadata($query) {
  if(!is_admin() && $query->is_main_query()) {
    if($query->is_search) {
      $query->set('meta_query', array(array(
        'key' => '_faqs',
        'value' => $query->query_vars['s'],
        'compare' => 'LIKE'
      )));
    }
  }
}
add_action('pre_get_posts', 'search_faqs_metadata');

and then (2) replace the AND with OR via the get_meta_sql filter:

function change_meta_key_where_from_and_to_or($sql) {
  if(is_search()) {
    $sql['where'] = substr($sql['where'], 1, 3) == 'AND' ? substr_replace($sql['where'], 'OR', 1, 3) : $sql['where'];
  }
  return $sql;
}
add_filter('get_meta_sql', 'change_meta_key_where_from_and_to_or');
share|improve this answer

Found a tutorial that might do the trick: http://www.deluxeblogtips.com/2012/04/search-all-custom-fields.html

The basic gist is to use custom mySQL queries to find the post ids of the posts with the proper meta, then feed those ids into the query:

function wpse143477_parse_clauses($clauses, $query) {
    if(!is_admin() && $query->is_main_query()) {
        if($query->is_search) {
            $keyword = $query->get('s');
            $keyword = '%' . like_escape( $keyword ) . '%';
            $post_ids = $wpdb->get_col( $wpdb->prepare( "
                SELECT DISTINCT post_id FROM {$wpdb->postmeta}
                WHERE meta_key = '_faqs' AND meta_value LIKE '%s'
            ", $keyword ) );
            if( !empty( $post_ids )) {
                $clauses['where'] .= " OR {$wpdb->posts}.ID IN (" . implode( ', ', $post_ids ) . ')';
            }
        }
    }

    return $clauses;
}
add_filter( 'posts_clauses', 'wpse143477_parse_clauses', 10, 2 );
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.