Total Count of Posts NOT in Selected Categories?

Question

I need to get the total number of posts that are not in selected categories. I have category IDs of the categories that I want to exclude from count (in the variable $folio_cat_ids)

So far I have this code which is not working,

$numposts = $wpdb->get_var("SELECT COUNT(*) FROM $wpdb->posts 
  WHERE post_status = 'publish' 
    AND post_type = 'post' 
    AND post_name NOT LIKE '%revision%' 
    AND post_name NOT LIKE '%autosave%' 
    AND post_category NOT IN($folio_cat_ids)");

Please help.

Answer 1

Do your posts have multiple categories? If not - I think individual category counts are cached, so can sum up counts:

$categories = get_categories( array( 'exclude' => $folio_cat_ids ) );

$count = 0;
foreach ( $categories as $category)
    $count += $category->category_count;

Can also just get and count the posts, that is more flexible and takes care of overlap, but also more resource-intensive:

$posts = get_posts( array(
    'category__not_in' => $folio_cat_ids,
    'numberposts' => '-1'
) );

$count = count( $posts );

Answer 2

Hi @Rich:

Welcome to WordPress Answers.

The 'post_category' field is not used anymore and has been replaced by a collection of "taxonomy" tables. If you were using direct SQL it would look like this:

$sql = <<<SQL
SELECT 
  COUNT(*) 
FROM 
  {$wpdb->posts} 
  INNER JOIN {$wpdb->term_relationships} ON {$wpdb->term_relationships}.object_id={$wpdb->posts}.ID
  INNER JOIN {$wpdb->term_taxonomy} ON {$wpdb->term_taxonomy}.term_taxonomy_id={$wpdb->term_relationships}.term_taxonomy_id
WHERE 1=1
  AND {$wpdb->posts}.post_status = 'publish' 
  AND {$wpdb->posts}.post_type = 'post' 
  AND {$wpdb->posts}.post_name NOT LIKE '%revision%' 
  AND {$wpdb->posts}.post_name NOT LIKE '%autosave%' 
  AND {$wpdb->term_taxonomy}.term_id NOT IN ($folio_cat_ids)
SQL;
$numposts = $wpdb->get_var($sql);

However, it's a good idea to use the WordPress API rather than direct SQL if you can avoid having to write lots of SQL when using the WordPress API, which in this case is very possible. I've written a small class for you called CountPostsCategoriesNotIn which encapsulates the 'posts_fields' hook you need to use. Calling this class is as simple as this:

$folio_cat_ids = '1,2,3';
$numposts = CountPostsCategoriesNotIn::count($folio_cat_ids);
echo "$numposts\n";

And here is that class which you can copy into your theme's functions.php file or use in the .php file of a plugin you might be writing:

class CountPostsCategoriesNotIn {
  static function count($categories) {
    $posts_fields = array(__CLASS__,'posts_fields');
    add_action('posts_fields',$posts_fields);
    $query = new WP_Query(array(
      'posts_per_page' => '-1',
      'category__not_in' => $categories,
    ));
    remove_action('posts_fields',$posts_fields);
    return $query->post->post_count;
  }
  static function posts_fields($field_list) {
    $field_list = 'COUNT(*) as post_count';
    return $field_list;
  }
}