Custom Taxonomies Incorrectly Counting Revisions?

Question

(Moderator's note: The original title was "The count of my custom taxonomy is incorrect; it's counting revisions")

Has anybody run into this before? I've added two custom taxonomies, and the count column of the wp_term_taxonomy table is being set incorrectly. It appears to be counting revisions in addition to the published post.

I've poked around in the WordPress core but that's a huge beast and I honestly barely know where to start looking for what updates the count for that table.

Answer 1

Hi @berkleebassist:

It's hard to verify your issue without admin access to your site and your database but I can give you some direction that might help.

There are two functions in /wp-includes/taxonomy.php that update taxonomy term count: wp_update_term_count_now() and _update_post_term_count(). They are located (in WordPress v3.0.1) at line 2454 and line 2049, respectively. Both of them call an action hook 'edited_term_taxonomy' just after they have updated the count. Both functions send the same two parameters a $term and a $taxonomy so you can treat this as just one hook to program.

Here's a shell of a function you can copy to your theme's functions.php file to update the count, just add the SQL that UPDATEs the count how you want it to be updated:

add_action('edited_term_taxonomy','yoursite_edited_term_taxonomy',10,2);
function yoursite_edited_term_taxonomy($term,$taxonomy) {
  global $wpdb;
  $sql = "...set this to UPDATE taxonomy term count how you want...";
  $wpdb->query($sql);
}

Let me know if you need more specific direction about writing the SQL command.

Also, here's a trac ticket that discusses something similar; it may be related:

• Misleading post count on taxonomy screen

Answer 2

Ok, have a SQL query worked out, but a new kink: I've got two taxonomies, and am now trying to figure out how to run this function with the correct query for each one. Here are each of my queries:

UPDATE wp_cln_term_taxonomy tt1
SET count =
(SELECT count(p.ID) FROM  wp_cln_term_relationships tr
LEFT JOIN wp_cln_posts p
   ON (p.ID = tr.object_id AND p.post_type = 'examples' AND p.post_status = 'publish')
WHERE tr.term_taxonomy_id = tt1.term_taxonomy_id)
WHERE tt1.taxonomy = 'example-cats'

And the second one:

UPDATE wp_cln_term_taxonomy tt1
SET count =
(SELECT count(p.ID) FROM  wp_cln_term_relationships tr
LEFT JOIN wp_cln_posts p
   ON (p.ID = tr.object_id AND p.post_type = 'ideas' AND p.post_status = 'publish')
WHERE tr.term_taxonomy_id = tt1.term_taxonomy_id)
WHERE tt1.taxonomy = 'idea-cats'

These each set the correct post counts, but can I just run both queries together always? Or is there a way I can fire off just one or the other, to lessen the load on MySQL?