Count posts in taxonomy

DescriptionWordpress snippet by - 04/16/11

This wordpress sql query counts posts or custom posts in a taxonomy under multiple terms, just add more terms separated by commas where term-slug and term-slug2 are.

This is very useful because the get_terms() and get_terms_by() functions return the count of posts associated with the queried term, and this includes posts in the trash bin. So to just count the published posts in a custom taxonomy use this sql query.

More InformationGoto Reference Page

Tags , , ,

<?php

global $wpdb;
$query = "
		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 = 'my_taxonomy' 
		AND	cat_terms.slug IN ('term_slug','term-slug2')
	";
	return $wpdb->get_var($query);
?>
  • Share
Submitted by: Adam J Nowak
http://hyperspatial.com

9 Responses to “Count posts in taxonomy”

  1. Very useful snippit of code. I put it in a function and slightly improved it so that it would work if wordpress install has a database without the prefix of wp_

    function count_posts_in_term($taxonomy, $term,$type="post"){
    global $wpdb;
    $query = "
    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 = '".$type."'
    AND cat_term_taxonomy.taxonomy = '".$taxonomy."'
    AND cat_terms.slug IN ('".$term."')
    ";
    return $wpdb->get_var($query);
    }

  2. Sweet, feel free to store your own snippets on the site if you need somewhere to store them

  3. hey i am in trouble can you help me. my custom taxonomies show draft+published posts but i only want publish post counts

  4. WHERE cat_posts.post_status = 'publish' - make sure your query has a publish

  5. can you please tell me how to use this code to use in my custom taxonomy

    do i need to replace something?

  6. replace my_taxonomy and the term slugs below it with your info, if you have more trouble please consult the wordpress codex

  7. actually my prob is that i use custom taxonomies and display their posts counts along with their terms

    but these show all the posts that are published as well as drafted.

    while the wordpress default taxonomy shows only the published post.

    this issue is only happening with custom taxonomy

    can you please provide code. and step to install that code

  8. Just insert this query within your functions.php or template file. I cant provide much more help than that, these snippets are for reference and not tested after posting

Comments and Feedback