Tags to Post-ID mysql query. Tag Search

Question

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.

Answer 1

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