Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

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 );
share|improve this answer
    
I am always getting same result, even if i have different categories in $folio_cat_ids. Are the objects cached or something. Although I am using wp_reset_query already. (I am using the second code as I have posts with multiple categories for some posts) –  RichZenMaster Oct 25 '10 at 12:43
    
In which format do you have categories stored in variable? category__not_in only takes an array of IDs. –  Rarst Oct 25 '10 at 13:35
    
yes it has comma separated list of category IDs –  RichZenMaster Oct 26 '10 at 5:41
    
Comma separated list is string, you need array for this argument. So do something like explode( ',', $folio_cat_ids ); to use it with category__not_in. –  Rarst Oct 26 '10 at 5:45
    
@Rarst - Calling get_posts() to return all posts is very heavy just to get a count, especially when there are lots of posts. In WP 3.1 I think there will be arguments that allow you to get a COUNT(*) without having to use a hook, but it's not here yet. –  MikeSchinkel Oct 26 '10 at 6:50
show 2 more comments

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;
  }
}
share|improve this answer
    
Hi Sir, Thanks a lot for solution. Can you please post a non OOP version of the code, just a function I mean. I have just started to learn OO PHP and I don't have enough knowledge to convert it into a basic function. Like when you populate $posts_fields in the first line, I don't get it :( –  RichZenMaster Oct 26 '10 at 11:02
    
@RichZenMaster - The $posts_fields is the "callable" value used by add_action() to find the hook to run (see: php.net/manual/en/function.is-callable.php); I put in a variable for when I later do remove_action() but it's just a convenience and would have done w/o OOP. Being pedantic, this isn't OOP, it's just using a class to encapsulate $posts_fields so you won't need a cryptic function name to minimize conflicts. It is a self-contained drop in, why do you need a basic function instead? If you still need a function I can, but this is a better approach. –  MikeSchinkel Oct 27 '10 at 20:26
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.