In the tag archive – display count of posts for each category they belong to

I remembered that once I had to do same thing and wrote a custom sql query… it was painful, as @Rarst said.

However I’ll post the function I wrote, and there isn’t much to explain, is a complex SQL query where there is the intersection of 2 checks to get all the term of a specific taxonomy that are associated in post having a specific term in another taxonomy:

function tax_mix_count( $term, $tax, $to_count, $post_type="" ) {
  if ( ! did_action( 'wp_loaded' ) && current_filter() !== 'wp_loaded' ) {
    return new WP_Error('too-early', 'Call tax_mix_count() after wp_loaded');
  }
  if ( ! taxonomy_exists( $tax ) || ! taxonomy_exists( $to_count ) ) {
    return new WP_Error('invalid-tax', 'Pass valid taxonomy names to tax_mix_count()');
  }
  $by = is_numeric( $term ) ? 'term_id' : 'slug';
  $term_obj = get_term_by( $by, $term, $tax );
  if ( empty( $term_obj ) || is_wp_error( $term_obj ) ) {
    return new WP_Error( "invalid-term", "Pass valid {$tax} term to tax_mix_count()" );
  }
  global $wpdb;
  $pt_where="";
  if ( is_string( $post_type ) && post_type_exists( $post_type )  ) {
    $pt_where = $wpdb->prepare(' AND p.post_type = %s', $post_type );
  }
  $query = "
    SELECT t.term_id FROM {$wpdb->term_taxonomy} tt
    INNER JOIN {$wpdb->terms} t ON tt.term_id = t.term_id
    INNER JOIN {$wpdb->term_relationships} tr ON tt.term_taxonomy_id = tr.term_taxonomy_id
    INNER JOIN {$wpdb->posts} p ON (p.ID = tr.object_id) 
    INNER JOIN {$wpdb->term_relationships} tr2 ON (p.ID = tr2.object_id)
    INNER JOIN {$wpdb->term_taxonomy} tt2 ON (tt2.term_taxonomy_id = tr2.term_taxonomy_id) 
    WHERE tt.taxonomy = %s
      AND p.post_status="publish"{$pt_where}
      AND tt2.taxonomy = %s AND tt2.term_id = %d";
  $c = $wpdb->get_col( $wpdb->prepare( $query, $to_count, $tax, $term_obj->term_id ) );
  if ( empty( $c ) ) return false;
  return array_count_values( $c );
}

As you can see it’s possible to use with different taxonomies and not only tags and categories.

• The first argument, $term can be a term id or a term slug.
• The second argument is the taxonomy the term belong.
• The third argument is the taxonomy to count.
• The fourth argument (optional) is the post type to limit the count.

Is possible do not to pass any post type as well if the two taxonomies are used by only one custom post type or if one want ot count all post types.

Note that if an invalid CPT is passed it is skipped without return any error.

The function also check if the hook ‘wp_loaded’ was already fired, because before that taxonomies and post types are not registered, so checks for taxonomies or post type existence will fail.

How to use:

$category_old_books = tax_mix_count( 'old', 'post_tag', 'category', 'books' );

if ( $category_old_books === FALSE ) {
  '<p>Posts in "old" tag are not associated with categories.</p>';
} elseif ( is_array( $category_old_books ) ) {
  foreach( $category_old_books as $id => $count ) {
    $term = get_term( $id, 'category' );
    $url = add_query_arg( array('tag'=>'old'), get_term_link($term,'category') );
    printf(
     _n(
       '%d book is from <a href="https://wordpress.stackexchange.com/questions/140406/%s">%s</a> category',
       '%d books are from <a href="https://wordpress.stackexchange.com/questions/140406/%s">%s</a> category.',
       $count,
       'your_textdomain'
      ),
      $count, esc_url( $url ), esc_html( $term->name )
    );
    echo '<br>';
  }
}

Of course replace ‘your_textdomain’ with your real text domain 😉