All geek questions in one place

Filter messages in the request if they have only one specific tag

Wordpress message requests in accordance with their tags may look like this (if I assembled them correctly, I removed the unnecessary parts from the request):

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 request all messages with tag ID 55.

What I need to do is filter out all messages containing only this single tag, but no other tags.

So, I want to show the message if it has tags 23,34,55,67 But I do not want to show the message if it has tag 55 (and no other tags). Messages that do not contain this specific tag should also be included in the request.

How to do it?

+6
source share
2 answers

Try adding a HAVING 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 the WHERE to check only the type and status of the message.

Also, if you do not select anything other than tag_id from the wp_terms attachment, this is optional, since you can simply use the term_id from wp_term_taxonomy.

+4
source

If you need all messages with a specific tag, then why did you specify the message identifier in the request?

In the next request, all messages with the specified tag identifier will be displayed

 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 these messages to have only the specified tag identifier, insert 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
+1
source

Source: https://habr.com/ru/post/908252/

More articles:


All Articles