Removing “trashed” posts from post count in custom taxonomy terms?

Question

I found a function that counts the number of posts in a custom taxonomy term. The problem is when I "trash" a post it still appears in the count. Any ideas on how to fix this?

Placed in functions.php:

//Job count

function wt_get_category_count($input = '') {
global $wpdb;
if($input == '')
{
$category = get_the_category();
return $category[0]->category_count;
}
elseif(is_numeric($input))
{
$SQL = "SELECT $wpdb->term_taxonomy.count FROM $wpdb->terms, $wpdb->term_taxonomy WHERE $wpdb->terms.term_id=$wpdb->term_taxonomy.term_id AND $wpdb->term_taxonomy.term_id=$input";
return $wpdb->get_var($SQL);
}
else
{
$SQL = "SELECT $wpdb->term_taxonomy.count FROM $wpdb->terms, $wpdb->term_taxonomy WHERE $wpdb->terms.term_id=$wpdb->term_taxonomy.term_id AND $wpdb->terms.slug='$input'";
return $wpdb->get_var($SQL);
}
}

Placed in the template file: (term located by id)

<?php echo wt_get_category_count('26'); ?>

Answer 1

Did you try adding to the queried object using post_status?

e.g.

$SQL = "SELECT $wpdb->term_taxonomy.count FROM $wpdb->terms, $wpdb->term_taxonomy WHERE $wpdb->terms.term_id=$wpdb->term_taxonomy.term_id AND $wpdb->term_taxonomy.term_id=$input AND post_status = 'publish'";

return $wpdb->get_var($SQL); }

Answer 2

This is a known, reported WordPress bug.

There are some workarounds, such as this one:

add_action( 'edited_term_taxonomy','wpse26548_edited_term_taxonomy', 10, 2 );
function wpse26548_edited_term_taxonomy($term,$taxonomy) {
  global $wpdb,$post;
  //in quick edit mode, $post is an array()
  //in full edit mode $post is an object
  if ( is_array( $post ))
    $posttype=$post['post_type'];
  else
    $posttype=$post->post_type;
  if ($posttype) {
    $DB_prefix=$wpdb->get_blog_prefix(BLOG_ID_CURRENT_SITE);
    $sql = "UPDATE ".$DB_prefix."term_taxonomy tt
          SET count =
          (SELECT count(p.ID) FROM  ".$DB_prefix."term_relationships tr
          LEFT JOIN ".$DB_prefix."posts p
          ON (p.ID = tr.object_id AND p.post_type = '".$posttype."' AND p.post_status = 'publish')
          WHERE tr.term_taxonomy_id = tt.term_taxonomy_id)
          WHERE tt.taxonomy = '".$taxonomy->name."'
      ";
    $wpdb->query($sql);
  }
}

You may also need to add the following to your register_taxonomy() arguments array:

'update_count_callback' => '_update_post_term_count'

Note that these are completely untested.

(See also: this related WPSE question)