Mysql custom output for wordpress

Question

I developed a taxonomy for the site I was working on that could abuse the categorization system for wordpress messages, classified as topics that they refer to (let’s say cats, dogs, monkeys), as well as what type of message (say, an expert , organization, article). Therefore, I would like to find all posts about cats and dogs, and these are organizations. Something like the IN lines (cats, dogs) AND IN (organizations) ... at least as it makes sense to me, but I can't figure out the correct SQL syntax for this task.

Based on what I found in this article on wordpress.com , I build from the query below ... but I'm not sure of the correct syntax for how to say: "I want something that belongs (category 1 or 2 ) and (belongs to category 3) (say, cat 1 = cats, 2 = dogs, 3 = organizations).

This is probably very simple, and I will kick myself when I get an answer.

SELECT * FROM $wpdb->posts
LEFT JOIN $wpdb->postmeta ON($wpdb->posts.ID = $wpdb->postmeta.post_id)
LEFT JOIN $wpdb->term_relationships ON($wpdb->posts.ID = $wpdb->term_relationships.object_id)
LEFT JOIN $wpdb->term_taxonomy ON($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
WHERE $wpdb->term_taxonomy.term_id = 1,2,3
AND $wpdb->term_taxonomy.taxonomy = 'category'
AND $wpdb->posts.post_status = 'publish'
ORDER BY $wpdb->postmeta.meta_value ASC

Thanks!

Answer 1

Since two conditions exist on different rows of the table term_taxonomy, you need to join this table twice to compare them on the same row of the result set.

, LEFT JOIN, WHERE. , .

, .

, , :

SELECT * FROM $wpdb->posts p
 INNER JOIN $wpdb->postmeta m ON (p.ID = m.post_id)
 INNER JOIN $wpdb->term_relationships r1 ON (p.ID = r1.object_id)
 INNER JOIN $wpdb->term_taxonomy x1
  ON (r1.term_taxonomy_id = x1.term_taxonomy_id)
 INNER JOIN $wpdb->term_relationships r2 ON (p.ID = r2.object_id)
 INNER JOIN $wpdb->term_taxonomy x2
  ON (r2.term_taxonomy_id = x2.term_taxonomy_id)
WHERE x1.term_id IN (1, 2) AND x1.taxonomy = 'category'
 AND x2.term_id = 3 AND x2.taxonomy = 'category'
 AND p.post_status = 'publish'
ORDER BY m.meta_value ASC