WP query taxonomy input differs to output?

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) )
)