Filter posts in a query if they have only one certain tag

Question

Querying wordpress posts according to their tags can look like this (if I pieced it together correctly - I removed irrelevant parts from the query):

SELECT wposts.ID AS ID,
wposts.post_title, wposts.post_status, wposts.post_name,
tag_terms.term_id AS tag_id

FROM `wp_posts` AS wposts

INNER JOIN wp_term_relationships AS tag_term_relationships ON (wposts.ID = tag_term_relationships.object_id)
INNER JOIN wp_term_taxonomy AS tag_term_taxonomy ON (tag_term_relationships.term_taxonomy_id = tag_term_taxonomy.term_taxonomy_id AND tag_term_taxonomy.taxonomy = 'post_tag')
INNER JOIN wp_terms AS tag_terms ON (tag_term_taxonomy.term_id = tag_terms.term_id)

WHERE wposts.ID = '12345'

AND wposts.post_type = 'post'

AND wposts.post_status NOT LIKE 'private'

AND tag_terms.term_id = '55'

GROUP BY wposts.ID
ORDER BY wposts.post_date ASC

This should query all posts with tag id 55.

What I need to do is to filter out all posts that only contain this single tag, but no other tags.

So I want to show a post if it has the tags 23,34,55,67 But I don't want to show the post if it has the tag 55 (and no other tags). Posts that do not contain this certain tag should be included in the query as well.

How do I do this?

Answer 1

Try adding HAVING condition between GROUP BY and ORDER BY:

...
GROUP BY wposts.ID
HAVING COUNT( tag_terms.term_id ) <> 1
  OR MAX( tag_terms.term_id ) <> 55
ORDER BY wposts.post_date ASC

and change your WHERE condition to check only for post type and status.

Also if you don't select anything other than tag_id from wp_terms joining it is not necessary as you can just use term_id from wp_term_taxonomy.

Answer 2

If you want all the posts with a certain tag, then why have you specified the post ID in the query ?

This following query will show all posts with the given tag id

SELECT wposts.ID AS ID,
wposts.post_title, wposts.post_status, wposts.post_name,
tag_terms.term_id AS tag_id

FROM `wp_posts` AS wposts

INNER JOIN wp_term_relationships AS tag_term_relationships ON (wposts.ID = tag_term_relationships.object_id)
INNER JOIN wp_term_taxonomy AS tag_term_taxonomy ON (tag_term_relationships.term_taxonomy_id = tag_term_taxonomy.term_taxonomy_id AND tag_term_taxonomy.taxonomy = 'post_tag')
INNER JOIN wp_terms AS tag_terms ON (tag_term_taxonomy.term_id = tag_terms.term_id)


WHERE wposts.post_type = 'post'

AND wposts.post_status NOT LIKE 'private'

AND tag_terms.term_id = '55'

GROUP BY wposts.ID
ORDER BY wposts.post_date ASC

Now, if you want to make sure that these posts have only the given tag id, nest the query as follows :

    SELECT wposts.ID AS ID,
    wposts.post_title, wposts.post_status, wposts.post_name,
    tag_terms.term_id AS tag_id

    FROM `wp_posts` AS wposts

    INNER JOIN wp_term_relationships AS tag_term_relationships ON (wposts.ID = tag_term_relationships.object_id)
    INNER JOIN wp_term_taxonomy AS tag_term_taxonomy ON (tag_term_relationships.term_taxonomy_id = tag_term_taxonomy.term_taxonomy_id AND tag_term_taxonomy.taxonomy = 'post_tag')
    INNER JOIN wp_terms AS tag_terms ON (tag_term_taxonomy.term_id = tag_terms.term_id)


    WHERE wposts.post_type = 'post'

    AND wposts.post_status NOT LIKE 'private'

    AND tag_terms.term_id = '55'

    GROUP BY wposts.ID
    HAVING COUNT( tag_terms.term_id ) <> 1
    OR MAX( tag_terms.term_id ) <> 55    
    ORDER BY wposts.post_date ASC