$wpdb: Counting posts corresponding to 3 terms in 3 different taxonomies

3 Views

It’s easier if I made an example:

I have a custom post type MYPOSTTYPE, with 3 taxonomies TAX_1, TAX_2 and TAX_3

Read More

If I have 3 terms: ‘TERM_TAX_1’ in TAX_1, ‘TERM_TAX_2’ in TAX_2, and ‘TERM_TAX_3’ in TAX_3, how can I count how many MYPOSTTYPE have all the 3 terms with a SQL query and $wpdb?

I’ve tried with something like this, but it’s not working… the counter is always at 0 

SELECT COUNT($wpdb->posts.ID) AS counter
FROM $wpdb->posts 
    LEFT JOIN $wpdb->term_relationships ON ($wpdb->posts.ID = $wpdb->term_relationships.object_id)
    LEFT JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
    LEFT JOIN $wpdb->terms AS tax_one ON ($wpdb->term_taxonomy.term_id = tax_one.term_id)
    LEFT JOIN $wpdb->terms AS tax_two ON ($wpdb->term_taxonomy.term_id = tax_two.term_id)
    LEFT JOIN $wpdb->terms AS tax_three ON ($wpdb->term_taxonomy.term_id = tax_three.term_id)
WHERE 1=1
    AND $wpdb->posts.post_type = 'MYPOSTTYPE'
    AND $wpdb->posts.post_status = 'publish'
    AND $wpdb->term_taxonomy.taxonomy  IN ('TAX_1', 'TAX_2', 'TAX_3')
    AND tax_one.slug = 'TERM_TAX_1' 
    AND tax_two.slug = 'TERM_TAX_1'
    AND tax_three.slug = 'TERM_TAX_1'

UPDATE: I’ve also tried with tax_query, but I had this problem
Problem with get_posts, tax_query and counting the number of posts

so I wanted to try to do a query directly to the database

Post Views: 3

Related posts

Leave a Reply

2 comments

  1. Do a tax-query and then count the result. No need for a custom query with dozens of JOINS.

    $posts = new WP_Query( array(
    'post_type' => 'MYPOSTTYPE', 
    'tax_query' => array(     
        array(         
            'taxonomy' => 'TAX_1',
            'field'    => 'slug',         
            'terms'    => array( 'TERM_TAX_1' ),
            'operator' => 'IN'
        ),
        array(
            // etc.
        )
    ),
    'post_status' => 'publish'
);
prinft(
     '<h3>COUNT: %s</h3>'
    ,count( $posts )
);

  2. Since MySQL allows to nest queries in where clause, I think a query like this should work

    SELECT COUNT($wpdb->posts.ID) AS counter
FROM $wpdb->posts 
    INNER JOIN $wpdb->term_relationships ON ($wpdb->posts.ID = $wpdb->term_relationships.object_id)
    INNER JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
    INNER JOIN $wpdb->terms ON ($wpdb->term_taxonomy.term_id = $wpdb->terms.term_id)
WHERE
    $wpdb->term_taxonomy.taxonomy = 'TAX_1'
    AND $wpdb->terms.slug = 'TERM_TAX_1'
    AND $wpdb->posts.ID IN ( SELECT $wpdb->posts.ID
FROM $wpdb->posts
    INNER JOIN $wpdb->term_relationships ON ($wpdb->posts.ID = $wpdb->term_relationships.object_id)
    INNER JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
    INNER JOIN $wpdb->terms ON ($wpdb->term_taxonomy.term_id = $wpdb->terms.term_id)
WHERE
    $wpdb->term_taxonomy.taxonomy = 'TAX_2'
    AND $wpdb->terms.slug = 'TERM_TAX_2'
    AND $wpdb->posts.ID IN ( SELECT $wpdb->posts.ID
FROM $wpdb->posts 
    INNER JOIN $wpdb->term_relationships ON ($wpdb->posts.ID = $wpdb->term_relationships.object_id)
    INNER JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
    INNER JOIN $wpdb->terms ON ($wpdb->term_taxonomy.term_id = $wpdb->terms.term_id)
WHERE
    $wpdb->posts.post_type = 'MYPOSTTYPE'
    AND $wpdb->posts.post_status = 'publish'
    AND $wpdb->term_taxonomy.taxonomy = 'TAX_3'
    AND $wpdb->terms.slug = 'TERM_TAX_3'
))