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 ?
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 😀
Hi Saliem,
Thanks for the feedback. 🙂 Nice to hear this is really useful.
Thanks
Dimuthu
Thanks a ton for the post — all I needed was that diagram and a sample query 🙂
thanks a lot!
Hello!
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
Brilliant – thanks for this! Just what I was looking for, saved me years of time.
It should be relatively easy to modify that query to search for a post with a particular tag AND category.
Nice one.
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. 🙂
thank you…i waiting this infomation…..:)