CODEWITHSUNDEEP
phpmysqlsqlwordpressplugins
yoavf
yoavf

Reputation: 21233

How to select posts with specific tags/categories in WordPress

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

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

I was told it's impossible because of 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"?

Upvotes: 10

Views: 8103

Answers (6)

Eric
Eric

Reputation: 4301

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

Upvotes: 3

Eric
Eric

Reputation: 4301

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

Upvotes: 5

Raghu
Raghu

Reputation:

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

Upvotes: 0

yoavf
yoavf

Reputation: 21233

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

Upvotes: 0

Scott Gottreu
Scott Gottreu

Reputation: 3676

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.

Upvotes: 1

Matt Rogish
Matt Rogish

Reputation: 24873

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

Upvotes: 2

Related Questions