I have a custom post type that uses several meta fields. In the admin area I would like to be able to search by those meta fields. I have implemented this currently in my functions.php with this code:
function custom_search_query( $query ) {
if ( is_admin() && is_main_query() && $query->is_search ) {
$query->set('meta_query', array(
"relation" => "OR",
array(
'key' => 'first_name',
'value' => $query->query_vars['s'],
'compare' => 'LIKE'
),
array(
'key' => 'last_name',
'value' => $query->query_vars['s'],
'compare' => 'LIKE'
),
array(
'key' => 'email',
'value' => $query->query_vars['s'],
'compare' => 'LIKE'
)
));
$query->set('post_type', 'application'); // optional
};
}
add_filter( 'pre_get_posts', 'custom_search_query');
The problem is that with those code if I search for say the name “walker” and I have records of my custom post type that match that criteria in the meta field I get back no results. The reason this appears to be happening is that the SQL that is being generated by the query that gets executed is this:
SELECT SQL_CALC_FOUND_ROWS wp_posts.ID 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)
INNER JOIN wp_postmeta AS mt2 ON (wp_posts.ID = mt2.post_id) WHERE 1=1
AND (((wp_posts.post_title LIKE '%walker%') OR (wp_posts.post_content LIKE '%walker%')))
AND wp_posts.post_type = 'application' 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')
AND ((wp_postmeta.meta_key = 'first_name' AND CAST(wp_postmeta.meta_value AS CHAR) LIKE '%walker%')
OR (mt1.meta_key = 'last_name' AND CAST(mt1.meta_value AS CHAR) LIKE '%walker%')
OR (mt2.meta_key = 'email' AND CAST(mt2.meta_value AS CHAR) LIKE '%walker%') )
GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 20
The query is attempting to search both the post title and content AND my custom fields. I don’t want it to be looking for matches in the title or content. How can I get the query to stop doing that and thereby returning the restuls that aI want?
There is a plugin called Search everything, where you can break down what you want to search. It does custom fields, posts and of course titles.