Mysql: Using "AND" Operation on tags and categories tables in Wordpress

Answer 1

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" 
                                           )
                            )
            )

Answer 2

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? ;)

Answer 3

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.

Answer 4

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')

Answer 5

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

Answer 6

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