<p>This is a very specific question regarding <strong>MySQL</strong> as implemented in <strong>WordPress</strong>.</p>
<p>I'm trying to develop a plugin that will show (select) posts that have specific '<strong>tags</strong>' and belong to specific '<strong>categories</strong>' (both multiple)</p>
<p>I was told it's impossible because of the way categories and tags are stored:</p>
<ol>
<li><code>wp_posts</code> contains a list of posts, each post have an &quot;ID&quot;</li>
<li><code>wp_terms</code> contains a list of terms (both categories and tags). Each term has a TERM_ID</li>
<li><code>wp_term_taxonomy</code> has a list of terms with their TERM_IDs and has a Taxonomy definition for each one of those (either a Category or a Tag)</li>
<li><code>wp_term_relationships</code> has associations between terms and posts</li>
</ol>
<p>How can I join the tables to get all posts with tags &quot;Nuclear&quot; <strong>and</strong> &quot;Deals&quot; that also belong to the category &quot;Category1&quot;?</p>

[{'answer_id': 28233, 'author': 'Matt Rogish', 'author_id': 2590, 'author_profile': 'https://Stackoverflow.com/users/2590', 'pm_score': 2, 'selected': False, 'text': '<p>What a gross DB structure.</p>\n\n<p>Anyway, I\'d do something like this (note I prefer EXISTS to joins, but you can re-write them as joins if you like; most query analyzers will collapse them to the same query plan anyway). You may have to do some additional juggling one way or another to make it work...</p>\n\n<pre><code>SELECT *\n  FROM wp_posts p\n WHERE EXISTS( SELECT *\n                 FROM wp_term_relationship tr\n                WHERE tr.object_id = p.id\n                  AND EXISTS( SELECT *\n                                FROM wp_term_taxonomy tt\n                               WHERE tt.term_taxonomy_id = tr.term_taxonomy_id\n                                 AND tt.taxonomy         = \'category\'\n                                 AND EXISTS( SELECT *\n                                               FROM wp_terms t\n                                              WHERE t.term_id = tt.term_id\n                                                AND t.name    = "Category1" \n                                           )\n                            )\n                  AND EXISTS( SELECT *\n                                FROM wp_term_taxonomy tt\n                               WHERE tt.term_taxonomy_id = tr.term_taxonomy_id\n                                 AND tt.taxonomy         = \'post_tag\'\n                                 AND EXISTS( SELECT *\n                                               FROM wp_terms t\n                                              WHERE t.term_id = tt.term_id\n                                                AND t.name    = "Nuclear" \n                                           )\n                                 AND EXISTS( SELECT *\n                                               FROM wp_terms t\n                                              WHERE t.term_id = tt.term_id\n                                                AND t.name    = "Deals" \n                                           )\n                            )\n            )\n</code></pre>\n'}, {'answer_id': 28373, 'author': 'Eric', 'author_id': 2610, 'author_profile': 'https://Stackoverflow.com/users/2610', 'pm_score': 2, 'selected': False, 'text': "<p>Try this:</p>\n\n<pre><code>select p.*\nfrom wp_posts p, \nwp_terms t, wp_term_taxonomy tt, wp_term_relationship tr\nwp_terms t2, wp_term_taxonomy tt2, wp_term_relationship tr2\n\nwhere p.id = tr.object_id\nand t.term_id = tt.term_id\nand tr.term_taxonomy_id = tt.term_taxonomy_id\n\nand p.id = tr2.object_id\nand t2.term_id = tt2.term_id\nand tr2.term_taxonomy_id = tt2.term_taxonomy_id\n\nand (tt.taxonomy = 'category' and tt.term_id = t.term_id and t.name = 'Category1')\nand (tt2.taxonomy = 'post_tag' and tt2.term_id = t2.term_id and t2.name in ('Nuclear', 'Deals'))\n</code></pre>\n\n<p>Essentially I'm employing 2 copies of the pertinent child tables - terms, term_taxonomy, and term_relationship.  One copy applies the 'Category1' restriction, the other the 'Nuclear' or 'Deals' restriction.</p>\n\n<p>BTW, what kind of project is this with posts all about nuclear deals?  You trying to get us on some government list? ;)</p>\n"}, {'answer_id': 29167, 'author': 'Scott Gottreu', 'author_id': 2863, 'author_profile': 'https://Stackoverflow.com/users/2863', 'pm_score': 1, 'selected': False, 'text': '<p>So I tried both options on my WordPress db.  I looked for the category "Tech" in my posts with the tags "Perl" AND "Programming".</p>\n\n<p><a href="https://stackoverflow.com/users/2610/eric">Eric\'s</a> worked once I added a missing comma in the initial select statement.  It returned 3 records.  The problem is that the section that is looking for the "post_tag" is actually working as an OR option.  One of my posts only had one tag not both.  Also it would be good to make the SELECT DISTINCT.</p>\n\n<p>I tried <a href="https://stackoverflow.com/users/2590/matt">Matt\'s</a> version, but it kept returning an empty set.  I may try to "juggle" with it.</p>\n'}, {'answer_id': 30733, 'author': 'Eric', 'author_id': 2610, 'author_profile': 'https://Stackoverflow.com/users/2610', 'pm_score': 4, 'selected': True, 'text': "<p>I misunderstood you.  I thought you wanted Nuclear or Deals.  The below should give you only Nuclear and Deals.</p>\n\n<pre><code>select p.*\nfrom wp_posts p, wp_terms t, wp_term_taxonomy tt, wp_term_relationship tr,\nwp_terms t2, wp_term_taxonomy tt2, wp_term_relationship tr2\nwp_terms t2, wp_term_taxonomy tt2, wp_term_relationship tr2\n\nwhere p.id = tr.object_id and t.term_id = tt.term_id and tr.term_taxonomy_id = tt.term_taxonomy_id\n\nand p.id = tr2.object_id and t2.term_id = tt2.term_id and tr2.term_taxonomy_id = tt2.term_taxonomy_id\n\nand p.id = tr3.object_id and t3.term_id = tt3.term_id and tr3.term_taxonomy_id = tt3.term_taxonomy_id\n\nand (tt.taxonomy = 'category' and tt.term_id = t.term_id and t.name = 'Category1')\nand (tt2.taxonomy = 'post_tag' and tt2.term_id = t2.term_id and t2.name = 'Nuclear')\nand (tt3.taxonomy = 'post_tag' and tt3.term_id = t3.term_id and t3.name = 'Deals')\n</code></pre>\n"}, {'answer_id': 32017, 'author': 'yoavf', 'author_id': 1011, 'author_profile': 'https://Stackoverflow.com/users/1011', 'pm_score': 0, 'selected': False, 'text': '<p>Thanks @Eric it works! Just a few code corrections for future reference:</p>\n\n<ul>\n<li>the first select statements misses a coma after wp_term_relationship tr2</li>\n<li>In the same select statemt the following must be change:</li>\n</ul>\n\n<pre><code>wp_terms t2, wp_term_taxonomy tt2, wp_term_relationship \n\ntr2</code></pre>\n\n<p>should be</p>\n\n<pre><code>wp_terms t3, wp_term_taxonomy tt3, wp_term_relationship \n\ntr3</code></pre>\n'}, {'answer_id': 516351, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 0, 'selected': False, 'text': '<p>Really so great answer .. helped me a lot..</p>\n\n<p>great bcoz., it gave me basic approach to build my complex query !</p>\n\n<p>one small correction, for ready users like me :)</p>\n\n<p>"wp_term_relationship" will give \'doesn\'t exist error\' \n.. use <strong>wp_term_relationships</strong> as it is the correct table name.</p>\n\n<p>Thanks Eric</p>\n'}]
['https://Stackoverflow.com/questions/28196', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/1011/']