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.

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.

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

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:

SELECT p.ID 
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
share|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? –  proxylittle Jan 22 '11 at 18:10
    
@proxylittle yes, seems so. –  Rarst Jan 22 '11 at 18:20
    
ok ive tested it. Works like a charm! Thank you! –  proxylittle Jan 22 '11 at 18:39
add comment

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.