query multiple taxonomy and show post count

I’m sure that a custom sql query would work much better but here is an option using the WordPress Tools available

//first get all categories
$categories = get_terms( 'category', array(
    'orderby'    => 'count',
));

//then create an array for easier processing
foreach ( $categories as $cat ) {
       $slugs[] = $cat->slug;
       $counts[$cat->slug]['count'] = $cat->count;
}

//then loop over the categories and for each one create a "query" to count the number of available products
foreach($slugs as $term){
    $products = get_posts(array(
        'post_type' => 'product',
        'posts_per_page' => -1,
        'tax_query' => array(
            'relation' => 'AND',
            array(
                'taxonomy' => 'category',
                'field' => 'slug',
                'terms' => array( $term)
            ),
            array(
                'taxonomy' => 'product_status',
                'field' => 'slug',
                'terms' => array( "available"),
                'operator' => 'NOT IN',
            )
        )   
    ));
    $counts[$term]['available'] = count($products);
}

//then all the is left is to print everyting Out
if (count($counts) > 0){
    echo '<table><tr><td>Category</td><td>No. of products</td><td>Products available</td></tr>';
    foreach ($counts as $key => $val){
        echo '<tr><td>'.$key.'</td><td>'.$var['count'].'</td><td>'.$var['available'].'</td></tr>';
    }
    echo '</table>';
}

Leave a Comment