Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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

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

share|improve this question

2 Answers 2

Do a 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 )
);
share|improve this answer
    
I was trying with tax_query but I had this problem: wordpress.stackexchange.com/questions/69973/… so I wanted to try to do a query directly to the database –  Stefano Oct 22 '12 at 16:50
3  
Please! stick with one question! We're working hard to keep the "no answers" score low. Solve your old one first, then add a new one. That's how it works... –  kaiser Oct 22 '12 at 17:20

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'
))
share|improve this answer
    
nope but thanks! even this query keep returning a count of 0 :( and I don't know what I'am doing wrong... –  Stefano Oct 22 '12 at 20:22

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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