Select from term_taxnomy table using AND

1 Views

I have stucked with a situation, where i have to show posts on check and uncheck of the terms.

The posts having terms assigned. I have terms ‘Area’ and ‘Cuisines’ now i have to select the post which has the area ‘XYZ’ and cuisine ‘ABC’.

Read More

The query i have 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 the Structure of the wp_term_taxonomy is shown here :-

The problem is the single table and single column and apply the AND operator 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
Post Views: 1

Related posts

Leave a Reply

2 comments

  1. Suppose, we have 3 tables:

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

    Exactly 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 test1 table, that have rows in test1_to_test2 with (test2_id = 1) AND (test2_id = 2). So, we want this:

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

    To do it we split the task into 2 subtasks:

    1.Select test1_id from test1_to_test2, that has both rows present:

    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 IN operator (it’s the SQL, that 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’ll get the posts which have the area ‘XYZ’ and cuisine ‘ABC’.

  2. Your best bet is to do two joins, 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 ;

    Also, you can change the left joins to inner joins — which the where clause does anyway. The left joins would be useful for “or” conditions rather than “and” conditions.