Admin Area Custom Type Search By Meta Fields Without Title & Content

Question

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?

Answer 1

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.

Answer 2

function custom_post_type_search($query) {
    global $pagenow, $post_type;

    // Check if we are on the admin screen and it's the main query for the custom post type
    if (is_admin() && $pagenow == 'edit.php' && $post_type == 'application') {

        // Check if a search query is present
        if (!empty($_GET['s'])) {

            // Define an array of custom meta fields you want to include in the search
            $meta_fields = array('first_name', 'last_name', 'email');

            // Build the meta query
            $meta_query = array('relation' => 'OR');

            foreach ($meta_fields as $meta_field) {
                $meta_query[] = array(
                    'key'     => $meta_field,
                    'value'   => $_GET['s'],
                    'compare' => 'LIKE',
                );
            }

            // Exclude title and content from the search
            $query->set('s', ''); // Clear the default search
            $query->set('search_fields', array('meta')); // Search only in meta fields

            // Add the meta query to the main query
            $query->set('meta_query', $meta_query);
        }
    }
}

// Hook into the pre_get_posts action
add_action('pre_get_posts', 'custom_post_type_search');

In the code we are using above, search query is present ($_GET['s'] is not empty), it creates a meta query for the specified meta fields.

It excludes the default title and content search by setting $query->set('search_fields', array('meta')).