I need help with a MySQL query for WordPress database structure.
I’m building a custom plugin that needs to fetch posts based on multiple criteria. Specifically, I want to get posts that have certain tags AND belong to certain categories at the same time.
Someone mentioned this is tricky because of how WordPress stores this data:
wp_posts table stores all posts with unique IDs
wp_terms table contains all taxonomy terms (tags and categories) with TERM_IDs
wp_term_taxonomy table links terms to their taxonomy type (tag or category)
wp_term_relationships table connects posts to terms
For example, I want to retrieve all posts that have both tags “Science” and “Technology” while also being in category “News”.
What’s the correct way to join these tables together? I’ve tried a few approaches but keep getting incorrect results or no results at all.
Been wrestling with this exact problem for months on a client project. The breakthrough was realizing you need to count matching terms to ensure ALL required tags are present, not just any of them. Most people screw this up by using simple IN clauses which give you posts with ANY of the tags instead of ALL tags. Here’s my approach using subqueries with proper counting: SELECT p.* FROM wp_posts p WHERE p.post_status = ‘publish’ AND p.post_type = ‘post’ AND (SELECT COUNT(*) FROM wp_term_relationships tr JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id JOIN wp_terms t ON tt.term_id = t.term_id WHERE tr.object_id = p.ID AND tt.taxonomy = ‘post_tag’ AND t.slug IN (‘science’, ‘technology’)) = 2 AND p.ID IN (SELECT tr2.object_id FROM wp_term_relationships tr2 JOIN wp_term_taxonomy tt2 ON tr2.term_taxonomy_id = tt2.term_taxonomy_id JOIN wp_terms t2 ON tt2.term_id = t2.term_id WHERE tt2.taxonomy = ‘category’ AND t2.slug = ‘news’). The counting part is crucial - it ensures posts have exactly both required tags.
Your problem is handling multiple term relationships without messing up the results. I hit this same issue building a custom search feature last year. Use EXISTS clauses instead of trying to join everything - it checks each condition separately and avoids cartesian products that screw up your data. Here’s what worked for me: SELECT DISTINCT p.ID, p.post_title FROM wp_posts p WHERE p.post_status = ‘publish’ AND p.post_type = ‘post’ AND EXISTS ( SELECT 1 FROM wp_term_relationships tr JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id JOIN wp_terms t ON tt.term_id = t.term_id WHERE tr.object_id = p.ID AND tt.taxonomy = ‘post_tag’ AND t.slug IN (‘science’, ‘technology’) GROUP BY tr.object_id HAVING COUNT(DISTINCT t.term_id) = 2 ) AND EXISTS ( SELECT 1 FROM wp_term_relationships tr2 JOIN wp_term_taxonomy tt2 ON tr2.term_taxonomy_id = tt2.term_taxonomy_id JOIN wp_terms t2 ON tt2.term_id = t2.term_id WHERE tr2.object_id = p.ID AND tt2.taxonomy = ‘category’ AND t2.slug = ‘news’ )
honestly, WP_Query handles this way better than raw MySQL. use tax_query with relation AND - just create a new WP_Query and set up your tax_query array with the tag and category conditions you need. saves you from messy join logic since WordPress handles the relationships automatically.