WordPress Database – Relationship with Tags, Catagories and Posts

WordPress has a very simple database schema. And it is well documented. You can access the complete description of the wordpress core database from here, http://codex.wordpress.org/Database_Description.

Anyway first time I looked at the database I was confused with the term and the term_taxonomy table, why we need two tables for term and taxonomies. In fact in wordpress, the table ‘posts’ is associated with the table ‘term_taxonomy’ and not the table ‘term’ itself. In the term taxonomy table the terms are associated to a link category, post category or a tag. So the associations of posts to a tag or category is something like this.

wp_term_post association

wp_term_post association

So in a case you try querying for posts with a given tag it will be like this. (Note that I have skipped the optional database table prefix which is by default ‘wp_’)

SELECT post_title,
FROM posts p,
     terms t,
     term_relationships r,
     term_taxonomy tt
WHERE p.post_status='publish' AND
      tt.taxonomy = 'post_tag' AND
      p.id=r.object_id AND
      r.term_taxonomy_id=tt.term_taxonomy_id AND
      tt.term_id = t.term_id AND t.name LIKE ?
This entry was posted in database, design, php, Tutorial/Guide, wordpress and tagged , , , , , , . Bookmark the permalink.

9 Responses to WordPress Database – Relationship with Tags, Catagories and Posts

  1. Saliem says:

    this is really helpful! I was looking for something like this sort of documentation for the past day or two for just this thing almost exactly 😀 Thank you 😀

  2. dimuthu says:

    Hi Saliem,
    Thanks for the feedback. 🙂 Nice to hear this is really useful.


  3. Sudipta Chatterjee says:

    Thanks a ton for the post — all I needed was that diagram and a sample query 🙂

  4. Enrico says:

    thanks a lot!

  5. RaiulBaztepo says:

    Very Interesting post! Thank you for such interesting resource!
    PS: Sorry for my bad english, I’v just started to learn this language 😉
    See you!
    Your, Raiul Baztepo

  6. Kit Allen says:

    Brilliant – thanks for this! Just what I was looking for, saved me years of time.

  7. KJ says:

    It should be relatively easy to modify that query to search for a post with a particular tag AND category.

    Nice one.

  8. Marj Wyatt says:

    Thanks so much for making this clear and easy to understand. I’m working on a site right now where EXACTLY this information was needed. Like you, I was perplexed at the relationships in the tables and my SQL immersion is dated, at best. You saved my brain from hurting too much and it was easy to find using the right google search.

    Well Done! I Stumbled your post too. 🙂

  9. sanjay modi says:

    thank you…i waiting this infomation…..:)

Leave a Reply

Your email address will not be published. Required fields are marked *