Select term_taxnomy from the table using AND

Question

I encountered a situation where I need to show messages when checking and removing conditions from them.

Posts with assigned terms. I have the terms 'Area' and 'Cuisines', now I need to select a post that has an “XYZ” area and an “ABC” kitchen.

The request I tried: -

SELECT p.ID, p.post_title FROM wp_posts p LEFT JOIN `wp_term_relationships` t ON p.ID = t.object_id LEFT JOIN `wp_term_taxonomy` tt ON t.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.term_id IN (".$area.") OR tt.term_id IN (".$cuis.") GROUP BY t.object_id HAVING COUNT( t.term_taxonomy_id ) = 2 LIMIT 0,7 

And here is the wp_term_taxonomy structure shown: -

The problem is a single table and a separate column and the AND operator is applied between the values.

wp_term_relationship

 object_id | wp_term_taxonomy_id | term_order ============================================== 134 | 36 | 0 ______________________________________________ 135 | 36 | 0 

wp_posts

  ID | post_title | ================================== 1 | Hello world! | __________________________________ 2 | Test | 

wp_term_taxnomy

  term_taxonomy_id term_id taxonomy description parent count ============================================================================= 1 1 category ------ 0 2 

Answer 1

Suppose we have 3 tables:

 | test1 | | test1_to_test2 | | test2 | |-------+ +----------------| +-------| | id |-----| test1_id | +----| id | | test2_id |----+ 

It is the structure that you have.

Content:

  test1 +----+-------+ | id | value | +----+-------+ | 1 | val1 | | 2 | val2 | +----+-------+ test1_to_test2 |----------+----------| | test1_id | test2_id | |----------+----------| | 1 | 1 | | 1 | 2 | | 2 | 1 | |----------+----------| test2 |----+ | id | |----+ | 1 | | 2 | |----+ 

And we need to select values ​​from table test1 that have rows in test1_to_test2 with (test2_id = 1) AND (test2_id = 2). So we want:

 +----+-------+ | id | value | +----+-------+ | 1 | val1 | +----+-------+ 

To do this, we will divide the task into two subtasks:

1.Select test1_id from test1_to_test2, which contains both lines:

 SELECT test1_id FROM test1_to_test2 WHERE test1_to_test2.test2_id IN (1,2) GROUP BY test1_id HAVING COUNT(test1_id) = 2 

2.Select the appropriate rows from test1 using the subquery and the IN statement (this is the SQL we need):

 SELECT test1.id, test1.`value` FROM test1 WHERE test1.id IN ( SELECT test1_id FROM test1_to_test2 WHERE test1_to_test2.test2_id IN (1,2) GROUP BY test1_id HAVING COUNT(test1_id) = 2 ) 

We get what we need:

 +----+-------+ | id | value | +----+-------+ | 1 | val1 | +----+-------+ 

Use the same approach with your tables, and you will receive messages with the "XYZ" area and the "ABC" kitchen.

Answer 2

It is best to make two associations, one for each term:

  SELECT p.ID, p.post_title FROM wp_posts p LEFT JOIN `wp_term_relationships` t ON p.ID = t.object_id LEFT JOIN `wp_term_taxonomy` tarea ON t.term_taxonomy_id = tt.term_taxonomy_id and tt.term_id IN (".$area.") LEFT JOIN `wp_term_taxonomy` tcuis ON t.term_taxonomy_id = tt.term_taxonomy_id and tt.term_id IN (".$cuis.") WHERE tarea.description = 'XYZ' and tcuis.descriptoin = 'ABC' GROUP BY t.object_id LIMIT 0,7 ; 

Alternatively, you can change the left join to inner join as the where clause. left join would be useful for "or" conditions, not for "and" conditions.