5

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

  • Can you please post your entire code for the query. Are you using the_query($args); or $query = new WP_Query($args)? Yon mention $query->request in your question, so I'll assume the latter. If that is the case, you may have filters/actions in play that are affecting the query. – David Gard Jan 29 '14 at 13:00
  • This is expected, if you go through the long journey from WP_Query() to WP_Tax_Query() ;-) I will post you the "why" later today. – birgire Jan 29 '14 at 13:04
5

A curious journey of a "cat"

Let's assume we have the following category hierarchy:

enter image description here

where the relevant rows from the wp_term_taxonomy table are:

wp_term_taxonomy

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:

enter image description here

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) )
)
| improve this answer | |
  • 1
    Best answer ever? Thanks birgire that's the answer I was after x100 – Tim Jan 29 '14 at 15:52
  • Can I invoke WP_Tax_Query manually then and get the snippet of SQL to append to my larger query? – Tim Jan 29 '14 at 15:56
  • 1
    Yes, you can call it manually to generate the where+join SQL parts. Check out this pastebin. – birgire Jan 29 '14 at 16:07
  • 1
    ps: I added it to the answer, in case the pastebin link goes down ;-) – birgire Jan 29 '14 at 16:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.