CPT Meta Searching

Question

I'm developing a WP plugin which uses custom post types and meta data. What I'm trying to achieve is having the meta data searchable. Here is some code that I found which searches the meta data fine, however I can't get it to display the search string on the CPT page.

add_filter( "pre_get_posts", "custom_search_query");
function custom_search_query( $query ) {
    $custom_fields = array(
        "pctracker_company",
        "pctracker_customer",
        "pctracker_phone1",
        "pctracker_phone2",
        "pctracker_mobile",
        "pctracker_email"

    );
    $searchterm = $query->query_vars['s'];
    $query->query_vars['s'] = "";
    if ($searchterm != "") {
        $meta_query = array('relation' => 'OR');
        foreach($custom_fields as $cf){
            array_push($meta_query, array(
                'key' => $cf,
                'value' => $searchterm,
                'compare' => 'LIKE'
            ));
        }
        $query->set("meta_query", $meta_query);
    };
}

I know that in the code its unsetting 'S', however if you don't unset it, it does not display any results.

Can anyone point me in the right direction?

Thanks very much, Jason

EDIT:

I've just been having a play around and I've got this working!

function my_search_results($query){
    if(isset($_GET['s'])){
        if($_GET['post_type'] == "pctracker_customers") {
            $mySearch = $_REQUEST['s'];
            $query = 'select * from wp_posts,wp_postmeta WHERE wp_posts.ID = wp_postmeta.post_id AND wp_postmeta.meta_key = "pctracker_phone1" AND wp_postmeta.meta_value LIKE "%'.$mySearch.'%" AND(wp_posts.post_status = "publish")';
        }
    }
    return $query;
}
add_filter( 'posts_request', 'my_search_results');

EDIT 2: I've got multiple meta fields I need to search, pctracker_phone1 and pctracker_phone2 are just a couple. I've edited the SQL code so it searches for both. However the SQL code is going to be massive once all of the other metafields are added. Do you know how I could simplifier this:

$query = 'SELECT * FROM '. $wpdb->prefix .'posts,'. $wpdb->prefix .'postmeta WHERE '. $wpdb->prefix .'posts.ID = '. $wpdb->prefix .'postmeta.post_id AND '. $wpdb->prefix .'postmeta.meta_key = "pctracker_phone1" AND '. $wpdb->prefix .'postmeta.meta_value LIKE "%'.$mySearch.'%" OR '. $wpdb->prefix .'posts.ID = '. $wpdb->prefix .'postmeta.post_id AND '. $wpdb->prefix .'postmeta.meta_key = "pctracker_phone2" AND '. $wpdb->prefix .'postmeta.meta_value LIKE "%'.$mySearch.'%" AND('. $wpdb->prefix .'posts.post_status = "publish") ORDER BY '. $wpdb->prefix .'posts.post_date DESC'; 

Answer 1

The code you found modifies the main query on pre_get_posts and unsets the search terms so WP_Query doesn't run a real search query. What you want is

// Filter the search SQL that is used in the WHERE clause of WP_Query.
apply_filters_ref_array( 'posts_search', array( $search, &$this ) );

which runs previously in WP_Query::get_posts(). This later on adds to the WHERE clause:

$where .= $search . $whichauthor . $whichmimetype;

where the author part is:

" AND ($wpdb->posts.post_author = " . absint($q['author']) . ')';

and the MIME-Type part uses wp_post_mime_type_where() and is

$whichmimetype = wp_post_mime_type_where( $q['post_mime_type'], $wpdb->posts );

If the s query var is set, you can use orderby as well and modify it from a filter:

$search_orderby = apply_filters( 'posts_search_orderby', $search_orderby, $this );

The result of this will be the first ORDER BY SQL argument followed by whatever was specified as default ORDER BY argument. If there was no default, it will fall back to the search argument.

$orderby = $orderby ? $search_orderby . ', ' . $orderby : $search_orderby;

Of course you still can then use the posts_clauses and the related filters. You do an early hands on inspection of your results to check if you managed to get a working query by hooking in on

do_action( 'posts_selection', $where . $groupby . $orderby . $limits . $join );

This will give you the complete final SQL string.

Keep in mind that other plugins might as well jump in on the pre_get_posts, posts_request or posts_clauses, posts_* filters and that you won't really be able to work around such conflicts in a generic way.

Answer 2

I've got multiple meta fields I need to search, pctracker_phone1 and pctracker_phone2 are just a couple. I've edited the SQL code so it searches for both. However the SQL code is going to be massive once all of the other metafields are added. Do you know how I could simplifier this:

function my_search_results($query){
if(isset($_GET['s'])){
    if($_GET['post_type'] == "pctracker_customers") {
        $mySearch = $_REQUEST['s'];
        $query = 'SELECT * FROM '. $wpdb->prefix .'posts,'. $wpdb->prefix .'postmeta WHERE '. $wpdb->prefix .'posts.ID = '. $wpdb->prefix .'postmeta.post_id AND '. $wpdb->prefix .'postmeta.meta_key = "pctracker_phone1" AND '. $wpdb->prefix .'postmeta.meta_value LIKE "%'.$mySearch.'%" OR '. $wpdb->prefix .'posts.ID = '. $wpdb->prefix .'postmeta.post_id AND '. $wpdb->prefix .'postmeta.meta_key = "pctracker_phone2" AND '. $wpdb->prefix .'postmeta.meta_value LIKE "%'.$mySearch.'%" AND('. $wpdb->prefix .'posts.post_status = "publish") ORDER BY '. $wpdb->prefix .'posts.post_date DESC'; 
    }
}
return $query;
}
add_filter( 'posts_request', 'my_search_results');