How to count posts in multiple categories for WordPress

As we promised in WebD&D v2 introductory article, we’re doing a little series of articles about custom WordPress stuff we did to achieve certain features here on WebD&D. So here is the first one of the series…

One of the things that we needed throughout the site was a count of posts within various combinations of categories. Unfortunately, that kind of feature isn’t supported in WordPress out-of-the-box. We’ll get a little technical here so those of you who just want the final solution–feel free to fast forward to the end of the article and grab the code.

How we did it

WordPress is, as you probably know, a database driven platform, so first thing we need to do is to identify the right tables and understand their relationships in order to retrieve the correct data from them.

Basically our goal here is to identify terms that have taxonomy ‘category’ and filter the ones we’re interested in. For filtering only specific categories well need to join terms table. Then we have to find out which posts belong to those categories. That’s why we’ll join the wp_term_relationships table. And last but not least, we’ll throw posts table to the mix.

Let’s say we wanted to count all posts from “Advanced” and “Intermediate” categories. Assuming that table prefix is the default one, our SQL query should look something like:

SELECT	COUNT( DISTINCT cat_posts.ID ) AS post_count
FROM 	wp_term_taxonomy AS cat_term_taxonomy INNER JOIN wp_terms AS cat_terms ON
		cat_term_taxonomy.term_id = cat_terms.term_id
	INNER JOIN wp_term_relationships AS cat_term_relationships ON
		cat_term_taxonomy.term_taxonomy_id = cat_term_relationships.term_taxonomy_id
	INNER JOIN wp_posts AS cat_posts ON
		cat_term_relationships.object_id = cat_posts.ID
WHERE 	cat_posts.post_status = 'publish' AND
	cat_posts.post_type = 'post' AND
	cat_term_taxonomy.taxonomy = 'category' AND
	cat_terms.slug IN ('advanced', 'intermediate')

The name of the posts table is somewhat misleading because it contains not only posts but pages, post revisions, and whatnot… Hence the condition post_type = 'post'. Being picky like we are, we only want published posts so we expanded our condition with a little post_status = 'publish'.

Another thing I want to point out is the COUNT DISTINCT… A well know feature in WordPress is that a post can belong to any number of categories so without counting only distinct post IDs we could end up with a wrong (much bigger) count.

Now we’ll change that query a bit and wrap it in a PHP function. That will allow us to pass any number of category slugs.

function wdd_in_category_count($catslugs = '', $display = true) {
	global $wpdb;

	$post_count = 0;
	$slug_where = '';
	$catslugs_arr = split(',', $catslugs);

 	foreach ($catslugs_arr as $catslugkey => $catslug) {
		if ( $catslugkey > 0 ) {
			$slug_where .= ', ';
		 }

 		$slug_where .= "'" . trim($catslug) . "'";
	}

	$slug_where = "cat_terms.slug IN (" . $slug_where . ")";

	$sql =	"SELECT	COUNT( DISTINCT cat_posts.ID ) AS post_count " .
		"FROM 	" . $wpdb->term_taxonomy . " AS cat_term_taxonomy INNER JOIN " . $wpdb->terms . " AS cat_terms ON " .
				"cat_term_taxonomy.term_id = cat_terms.term_id " .
			"INNER JOIN " . $wpdb->term_relationships . " AS cat_term_relationships ON " .
				"cat_term_taxonomy.term_taxonomy_id = cat_term_relationships.term_taxonomy_id " .
			"INNER JOIN " . $wpdb->posts . " AS cat_posts ON " .
				"cat_term_relationships.object_id = cat_posts.ID " .
		"WHERE 	cat_posts.post_status = 'publish' AND " .
			"cat_posts.post_type = 'post' AND " .
			"cat_term_taxonomy.taxonomy = 'category' AND " .
			$slug_where;

	$post_count = $wpdb->get_var($sql);

	if ( $display ) {
		echo $post_count;
	} 

	return $post_count; 

}

Our little function here has a $catslugs input parameter that expects category slugs in a string separated with commas. We also used $wpdb variables instead of hard coding the table names, witch is always a good practice.

How can you use it

Download Multiple category post counter function for WordPress

First download the code, uncompress and paste it somewhere in your functions.php file. Then find the place in your template file where you want to display number of posts form certain categories and call the wdd_in_category_count() function.

For example, if you had a movie review blog and you wanted to display number of reviews for Horror and SF movies, put something like this:

<p>Total number of Horror and SF reviews: <?php wdd_in_category_count('sf, horror') ?>.</p>

And there you have it… That’s how you count sheep posts within multiple categories.

Please reply with your questions, comments and suggestions.