terms - SQL query joining on taxonomy tables not returning what I would expect

admin管理员组

文章数量:1402831

I want to get all posts that have a particular term of taxonomy A and a particular term of taxonomy B.

Here is the query I'm running:

        SELECT wp_posts.ID, wp_posts.post_title 
        FROM wp_posts 
        INNER JOIN wp_term_relationships ON wp_posts.ID = wp_term_relationships.object_id 
        INNER JOIN wp_term_taxonomy ON wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id 
        INNER JOIN wp_terms ON wp_term_taxonomy.term_id = wp_terms.term_id 
        WHERE (wp_term_taxonomy.taxonomy = 'category' AND wp_terms.slug in ('uncategorized'))
        AND (wp_term_taxonomy.taxonomy = 'post_tag' AND wp_terms.slug in ('red'))
        ORDER BY ID DESC

This returns 0 results despite querying on a DB / WP install that has a post with a category of "uncategorized" and a tag of "red"

I want to get all posts that have a particular term of taxonomy A and a particular term of taxonomy B.

Here is the query I'm running:

        SELECT wp_posts.ID, wp_posts.post_title 
        FROM wp_posts 
        INNER JOIN wp_term_relationships ON wp_posts.ID = wp_term_relationships.object_id 
        INNER JOIN wp_term_taxonomy ON wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id 
        INNER JOIN wp_terms ON wp_term_taxonomy.term_id = wp_terms.term_id 
        WHERE (wp_term_taxonomy.taxonomy = 'category' AND wp_terms.slug in ('uncategorized'))
        AND (wp_term_taxonomy.taxonomy = 'post_tag' AND wp_terms.slug in ('red'))
        ORDER BY ID DESC

This returns 0 results despite querying on a DB / WP install that has a post with a category of "uncategorized" and a tag of "red"

• taxonomy
• terms
• sql

Share Improve this question edited May 16, 2017 at 15:44 Anthony asked May 16, 2017 at 15:39 AnthonyAnthony 3111 silver badge55 bronze badges 4

• what is actually the question? obviously the query is not the same as how wordpress works.... You should use the WP API to generate a working query and then look the SQL being used – Mark Kaplun Commented May 16, 2017 at 16:33
• I assumed the SQL would be straightforward, so my question was why my valid query was returning 0 results when it should have been returning 1 result. The WP API is definitely a better way to go in this case as it indicated that the query is actually much more complex. – Anthony Commented May 16, 2017 at 18:11
• personally, I just don't care to invest the effort in understanding the details of SQL instruction, and if you are doing direct queries you might miss the benefits of caching. If you are doing things in wordpress itself IMO it is always easier and better to just use the APIs – Mark Kaplun Commented May 16, 2017 at 18:28
• I use the API often but in this case, i'm building a plugin based on another plugin that uses raw SQL. I will probably end up just converting the queries to use the API as my queries are much more complex then the original plugin. – Anthony Commented May 16, 2017 at 18:31

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 1

As @Mark Kaplun mentioned, the WP API can reveal the the queries performed

Below is a simple query using the API in a template to see what's going on:

<?php
/*
 * Template Name: Troubleshooting
 */

$args = array(
    'post_type' => 'post',
    'tax_query' => array(
        'relation' => 'AND',
        array(
            'taxonomy' => 'category',
            'field'    => 'slug',
            'terms'    => array( 'uncategorized' ),
        ),
        array(
            'taxonomy' => 'post_tag',
            'field'    => 'slug',
            'terms'    => array( 'red' ),
        ),
    ),
);
$query = new WP_Query( $args ); 
echo $query->request;

You're almost there but I just modified the SQL a bit and feels like the below solution should do the trick -

SELECT wp_posts.ID, wp_posts.post_title
FROM wp_posts
         INNER JOIN wp_term_relationships ON wp_posts.ID = wp_term_relationships.object_id
         INNER JOIN wp_term_taxonomy ON wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
         INNER JOIN wp_terms ON wp_term_taxonomy.term_id = wp_terms.term_id
WHERE wp_term_taxonomy.taxonomy IN ('category', 'post_tag')
  AND wp_terms.slug in ('uncategorized', 'red')
ORDER BY ID DESC;

本文标签： termsSQL query joining on taxonomy tables not returning what I would expect