Brtdku

This is a very specific question regarding MySQL as implemented in WordPress.

I'm trying to develop a plugin which will show (select) posts that have specific 'tags' and belong to specific 'categories' (both multiple)

I was told it's impossible because the way categories and tags are stored:

1. 
   wp_posts contains a list of posts, each post have an "ID"


2. 
   wp_terms contains a list of terms (both categories and tags). Each term has a TERM_ID


3. 
   wp_term_taxonomy 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)


4. 
   wp_term_relationships has associations between terms and posts

How can I join the tables to get all posts with tags "Nuclear" and "Deals" that also belong to the category "Category1"?

I misunderstood you. I thought you wanted Nuclear or Deals. The below should give you only Nuclear and Deals.

select p.*

from wp_posts p, wp_terms t, wp_term_taxonomy tt, wp_term_relationship tr,

wp_terms t2, wp_term_taxonomy tt2, wp_term_relationship tr2

wp_terms t2, wp_term_taxonomy tt2, wp_term_relationship tr2



where p.id = tr.object_id and t.term_id = tt.term_id and tr.term_taxonomy_id = tt.term_taxonomy_id



and p.id = tr2.object_id and t2.term_id = tt2.term_id and tr2.term_taxonomy_id = tt2.term_taxonomy_id



and p.id = tr3.object_id and t3.term_id = tt3.term_id and tr3.term_taxonomy_id = tt3.term_taxonomy_id



and (tt.taxonomy = 'category' and tt.term_id = t.term_id and t.name = 'Category1')

and (tt2.taxonomy = 'post_tag' and tt2.term_id = t2.term_id and t2.name = 'Nuclear')

and (tt3.taxonomy = 'post_tag' and tt3.term_id = t3.term_id and t3.name = 'Deals')

What a gross DB structure.

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...

SELECT *

  FROM wp_posts p

 WHERE EXISTS( SELECT *

                 FROM wp_term_relationship tr

                WHERE tr.object_id = p.id

                  AND EXISTS( SELECT *

                                FROM wp_term_taxonomy tt

                               WHERE tt.term_taxonomy_id = tr.term_taxonomy_id

                                 AND tt.taxonomy         = 'category'

                                 AND EXISTS( SELECT *

                                               FROM wp_terms t

                                              WHERE t.term_id = tt.term_id

                                                AND t.name    = "Category1" 

                                           )

                            )

                  AND EXISTS( SELECT *

                                FROM wp_term_taxonomy tt

                               WHERE tt.term_taxonomy_id = tr.term_taxonomy_id

                                 AND tt.taxonomy         = 'post_tag'

                                 AND EXISTS( SELECT *

                                               FROM wp_terms t

                                              WHERE t.term_id = tt.term_id

                                                AND t.name    = "Nuclear" 

                                           )

                                 AND EXISTS( SELECT *

                                               FROM wp_terms t

                                              WHERE t.term_id = tt.term_id

                                                AND t.name    = "Deals" 

                                           )

                            )

            )

So I tried both options on my WordPress db. I looked for the category "Tech" in my posts with the tags "Perl" AND "Programming".

Eric's 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.

I tried Matt's version, but it kept returning an empty set. I may try to "juggle" with it.

Try this:

select p.*

from wp_posts p, 

wp_terms t, wp_term_taxonomy tt, wp_term_relationship tr

wp_terms t2, wp_term_taxonomy tt2, wp_term_relationship tr2



where p.id = tr.object_id

and t.term_id = tt.term_id

and tr.term_taxonomy_id = tt.term_taxonomy_id



and p.id = tr2.object_id

and t2.term_id = tt2.term_id

and tr2.term_taxonomy_id = tt2.term_taxonomy_id



and (tt.taxonomy = 'category' and tt.term_id = t.term_id and t.name = 'Category1')

and (tt2.taxonomy = 'post_tag' and tt2.term_id = t2.term_id and t2.name in ('Nuclear', 'Deals'))

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.

BTW, what kind of project is this with posts all about nuclear deals? You trying to get us on some government list? ;)

Thanks @Eric it works! Just a few code corrections for future reference:

• the first select statements misses a coma after wp_term_relationship tr2


• In the same select statemt the following must be change:

wp_terms t2, wp_term_taxonomy tt2, wp_term_relationship 



tr2

should be

wp_terms t3, wp_term_taxonomy tt3, wp_term_relationship 



tr3

Really so great answer .. helped me a lot..

great bcoz., it gave me basic approach to build my complex query !

one small correction, for ready users like me :)

"wp_term_relationship" will give 'doesn't exist error'
.. use wp_term_relationships as it is the correct table name.

Thanks Eric