WP query taxonomy input differs to output?

Question

I'm trying to build a custom query and I need to include the current category ID. I previously had the WP_Query args as:

Array
(
    [post_status] => publish
    [posts_per_page] => -1
    [post_type] => courses
    [cat] => 70

When I output SQL result behind this argument query using $query->request; the taxonomies read as 95 and 203, instead of 70?

SELECT wp_posts.* FROM wp_posts 
            INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) 
            INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) 
            INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id) 
            INNER JOIN wp_postmeta AS mt2 ON (wp_posts.ID = mt2.post_id) 
            INNER JOIN wp_postmeta AS mt3 ON (wp_posts.ID = mt3.post_id) WHERE 1=1 
            AND ( wp_term_relationships.term_taxonomy_id IN (95,203)

Can anyone tell me how/why this is happening and what I need to do to find 95,203 from this initial 70 so that I can also build this query manually?

Many thanks

Answer 1

A curious journey of a "cat"

Let's assume we have the following category hierarchy:

where the relevant rows from the wp_term_taxonomy table are:

We want to query all posts in the animals category where the id is 65:

$query = new WP_Query( array( 'cat' => 65 ) );

and try to understand why the resulting SQL is:

SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID 
    FROM wp_posts  
    INNER JOIN wp_term_relationships 
        ON (wp_posts.ID = wp_term_relationships.object_id) 
    WHERE 1=1  
    AND ( wp_term_relationships.term_taxonomy_id IN (15,70, 75) ) 
        AND wp_posts.post_type = 'post' 
    AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') 
    GROUP BY wp_posts.ID 
    ORDER BY wp_posts.post_date 
    DESC LIMIT 0, 5

i.e. the part why 65 is changed into 15, 70, 75.

Let's start:

In WP_Query() our query is transformed into:

$tax_query[0] = array( 'taxonomy'           => 'category',
                       'terms'              =>  array( 65 ),
                       'field'              => 'term_id',
                       'include_children'   => 1
                   );

before it's feed to the hungry:

 WP_Tax_Query( $tax_query )

which generates the SQL query from the $tax_query array.

There it's merged with the defaults:

array( 'taxonomy'          => '',
       'terms'             => array(),
       'include_children'  => true,
       'field'             => 'term_id',
       'operator'          => 'IN',
);

When the WP_Query() object wants the resulting SQL query back it calls the WP_TAX_Query::get_sql() method.

Then our array is "cleaned" and "transformed":

a) It's cleaned via the WP_TAX_Query::clean_query() method, resulting in:

array( 'taxonomy'           => 'category',
       'terms'              =>  array( 61, 13, 65 ),
       'field'              => 'term_id',
       'include_children'   =>  1,
       'operator'           => 'IN',
);

where the child categories have been included with get_term_children().

b) It's transformed via the WP_TAX_Query::transform_query() method, where the term_id is transformed into the corresponding term_taxonomy_id values.

In our case it's the result of:

 SELECT term_taxonomy_id
 FROM wp_term_taxonomy
 WHERE taxonomy = 'category'
 AND term_id IN (15, 61, 65)

namely 15, 70 and 75:

Then our tax query looks like this:

array( 'taxonomy'           => 'category',
       'terms'              =>  array( 15, 70, 75 ),
       'field'              => 'term_taxonomy_id',
       'include_children'   =>  1,
       'operator'           => 'IN',
);

before it's added to the SQL query parts of WP_Query():

"join": INNER JOIN wp_term_relationships 
            ON (wp_posts.ID = wp_term_relationships.object_id)

"where": AND ( tfl_term_relationships.term_taxonomy_id IN (15,70,75) ) 

The end ;-)

---

Extra:

One can also play directly with the WP_TAX_Query class, to investigate the generated SQL.

For example:

    $tax_query = array();
    $tax_query[0] = array(
                   'taxonomy'           => 'category',
                   'terms'              =>  array( 65 ),
                   'field'              => 'term_id',
                   'include_children'   => 1
               );
    $t = new WP_TAX_Query( $tax_query );
    print_r( $t->get_sql( $GLOBALS['wpdb']->posts, 'ID' ) );

will give the following output:

Array
(
    [join] =>  INNER JOIN wp_term_relationships ON (tfl_posts.ID = wp_term_relationships.object_id)
    [where] =>  AND ( wp_term_relationships.term_taxonomy_id IN (15,70,75) )
)