Search / Filter posts on Title/Content OR Tags


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:

Query 1:

//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-> 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";

Query 2:

//the $s variable is for testing only
$s = 'cool';

SELECT $wpdb->posts.*
FROM $wpdb->posts, $wpdb->term_relationships, $wpdb->term_taxonomy, $wpdb->terms
WHERE ($wpdb-> 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.

– M

, , , Mestika 8 years 2013-07-24T14:22:55-05:00 0 Answers 93 views 0

Leave an answer