1

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.

1 Answer 1

2

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
2
  • 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, 2011 at 18:10
  • @proxylittle yes, seems so.
    – Rarst
    Jan 22, 2011 at 18:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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