Dimuthu’s Blog

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

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,
       post_content,
       post_date
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 ?