Single meta_query query using OR instead of AND in request's WHERE statement

Question

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?

Answer 1

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');

Answer 2

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 );