Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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

Query 2:

//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.

Cheers,
- M

share|improve this question

1 Answer 1

I worked it out myself..

The query I was looking goes like this:

SELECT *
FROM $wpdb->posts, $wpdb->term_relationships, $wpdb->term_taxonomy, $wpdb->terms
WHERE ($wpdb->terms.name LIKE '%$s%'
OR $wpdb->terms.slug 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

Cheers,
- M

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.