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

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

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

I was told it's impossible because the way categories and tags are stored:

  1. wp_posts contains a lists of post, each posts have an "ID"
  2. wp_terms contains a list of terms (both categories and tags). Eac 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_relationship has associations betweens 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" ?

Thanks!

This question and answers originated from www.stackoverflow.com
Question by (8/26/2008 2:29:38 PM)

Answer

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 by
Find More Answers
Related Topics  mysql  wordpress  query  plugins
Related Questions