search query within custom taxonomy term, post title and meta field

Question

I want to create a search that does:

1. Search within my custom taxonomy - custom-tags
2. search the post title
3. search select meta field ?? dont know
4. search with custom post type - easy part

I looked wordpress API but couldn't find any useful built in functions, i created a custom query below: there is bug here since it returns duplicate results sometime, maybe left join might be needed here.

this query searches for term and title only at moment, but i also want a meta field called cterm searched.

SELECT   wp_posts.*
    FROM wp_posts, wp_term_relationships, wp_term_taxonomy, wp_terms
    WHERE (wp_terms.name LIKE '%car%'
        OR wp_posts.post_title LIKE '%car%')
            AND wp_posts.post_status = 'publish'
            AND wp_posts.post_type = 'posts_vch'
        AND wp_posts.ID = wp_term_relationships.object_id
        AND wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
        AND wp_term_taxonomy.term_id = wp_terms.term_id  GROUP BY  wp_posts.ID  LIMIT  1 ,50

UPDATE:

above updated query, manage to remove duplicate result using group by BUT another bug is that it does NIT bring POSTs with term "car". Basically it only show posts that have the word "car" in the title but not in term/tag.

Answer 1

You can do everything you need using the search parameters allowed in WP_Query -- except searching in the post title only. In order to do that, see this answer.

Make sure to look at the taxonomy and meta subqueries allowed in the args passed to WP_Query.

EDIT: Since you need to select using OR, you have 2 options -- you could run 2 queries and merge the 2 post arrays afterwards, or if you are more concerned about performance, you can use a custom query and just execute the SQL you wrote above. Use the global $wpdb object and the function $wpdb->get_results(). In order to make your query not return duplicate results, you should be able to use SELECT DISTINCT.

Answer 2

Try this, it may help:

if( !defined( 'ABSPATH' ) )
    exit;

function modify_wp_search_where( $where ) {
    if( is_search() ) {
        global $wpdb, $wp;
        $where = preg_replace(
            "/($wpdb->posts.post_title (LIKE '%{$wp->query_vars['s']}%'))/i",
            "$0 OR ( $wpdb->postmeta.meta_value LIKE '%{$wp->query_vars['s']}%' )",
            $where
            );
        add_filter( 'posts_join_request', 'modify_wp_search_join' );
        add_filter( 'posts_distinct_request', 'modify_wp_search_distinct' );
    }
    return $where;      
}
add_action( 'posts_where_request', 'modify_wp_search_where' );

function modify_wp_search_join( $join ) {  
    global $wpdb;   
    return $join .= " LEFT JOIN $wpdb->postmeta ON ($wpdb->posts.ID = $wpdb->postmeta.post_id) ";   
}

function modify_wp_search_distinct( $distinct ) {   
    return 'DISTINCT';      
} 

Answer 3

For anyone who may have this issue in the future, the Wordpress codex has a sample of the kind of solution submitted here by @Alvin.

See https://codex.wordpress.org/Custom_Queries#Keyword_Search_in_Plugin_Table. Using $wpdb->posts etc. for inserting the table name will help simplify your code and make it more robust.