I need to make a custom query to look for posts (CPT) which match a certain keyword but it have to search posts in both the title, the content AND a custom taxonomy terms.
I’ve tried with to queries but I just can’t get it to work and I’ve of cause made sure that there are content in wordpress which match the criteria.
For clarification I’ll just explain the use case.
I got a CPT called products
The CPT has a custom taxonomy called details
There are approximately 100 details which all has a name and a description. The products itself also have a title, a description (the content) and some other fields.
At the product page, I want to have a “filter” function (Not the regular Wordpress search function!).
So if I filter the products by the keyword cool then it should query the CPT products and find the products which contains the word cool in either its title, its content or if it is marked with a taxonomy term which contains cool in it’s title / description.
My two queries I’ve tried looks like this:
//the $s variable is for testing only $s = 'cool'; $query = "SELECT $wpdb->posts.* FROM $wpdb->posts INNER JOIN $wpdb->term_relationships ON ($wpdb->posts.ID = $wpdb->term_relationships.object_id) INNER JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id) WHERE $wpdb->posts.post_content LIKE '%$s%' OR $wpdb->posts.post_title LIKE '%$s%' OR $wpdb->terms.name LIKE '%$s%') AND $wpdb->term_taxonomy.taxonomy = ‘details’ AND $wpdb->posts.post_type = ‘products’ AND $wpdb->posts.post_status = ‘publish’ GROUP BY $wpdb->posts.ID ORDER BY $wpdb->posts.post_date DESC";
//the $s variable is for testing only $s = 'cool'; $query=" SELECT $wpdb->posts.* FROM $wpdb->posts, $wpdb->term_relationships, $wpdb->term_taxonomy, $wpdb->terms WHERE ($wpdb->terms.name LIKE '%$s%' OR $wpdb->posts.post_content LIKE '%$s%' OR $wpdb->posts.post_title LIKE '%$s%') AND $wpdb->posts.ID = $wpdb->term_relationships.object_id AND $wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id AND $wpdb->term_taxonomy.term_id = $wpdb->terms.term_id AND $wpdb->posts.post_status = 'publish' AND $wpdb->posts.post_type 'products' ORDER BY $wpdb->posts.post_date DESC
I’ve also tried to log Wordpress’ own queries to find out how it queries a keyword filtering with a specific taxonomy selected but I haven’t been able to transfer it proper to my own code.
I hope someone can help me and any help or advice is very much appreciated.