Im trying to implement a tag-based search. When i specify certain tags, tagged posts will be searched and the post-id will be displayed that matches the searchcriteria.

Currently it only works for a single tag.

$query = "SELECT DISTINCT $wpdb->posts.ID FROM $wpdb->terms
    LEFT JOIN $wpdb->term_taxonomy ON ($wpdb->terms.term_id = $wpdb->term_taxonomy.term_id)
    LEFT JOIN $wpdb->term_relationships ON ($wpdb->terms.term_id = $wpdb->term_relationships.term_taxonomy_id)
    LEFT JOIN $wpdb->posts ON ($wpdb->term_relationships.object_id = $wpdb->posts.ID)
        WHERE $wpdb->term_taxonomy.taxonomy = 'post_tag' " . $substring;

Substring looks like following:

$substring = "AND $wpdb->terms.slug IN ('tag1','tag2')"

And here is the problem. It does not look for single posts that match all criteria. Instead it 'collects' all posts with all tags searched.

For example:

// postid1 -> tag1
// postid2 -> tag2
// postid3 -> tag1, tag2

$substring = "AND $wpdb->terms.slug IN ('tag1')"
//Output: postid1, postid3 - - - CORRECT
$substring = "AND $wpdb->terms.slug IN ('tag1','tag2')"
//Output: postid1, postid2, postid3 - - -WRONG!

//Expected: postid3

So far i have no idea howto solve it in a single mysql query. Maybe im missing something. Thanks for your help in advance.


These are normal IN mechanics - it matches anything in set, not all of set combined.

Type of match you want is called tag_slug__and in WP query arguments. You can see code that generates SQL for it in source of WP_Query->&get_posts() method.

Resulting SQL is like this:

FROM wp_posts p 
INNER JOIN wp_term_relationships tr ON (p.ID = tr.object_id) 
INNER JOIN wp_term_taxonomy tt ON (tr.term_taxonomy_id = tt.term_taxonomy_id) 
INNER JOIN wp_terms t ON (tt.term_id = t.term_id) 
WHERE tt.taxonomy = 'post_tag' 
AND t.slug IN ('is', 'filler') 
GROUP BY p.ID HAVING count(p.ID) = 2
| improve this answer | |
  • ok i understand. So when i search more then 2 tags i have to change the count plus list am i right? – Tom Siwik Jan 22 '11 at 18:10
  • @proxylittle yes, seems so. – Rarst Jan 22 '11 at 18:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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