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 ofWP_Query->&get_posts()
method.Resulting SQL is like this: