0

Short description: I've created a bunch of terms and attached them to an existing taxonomy for a certain custom-post that already exist using the SQL code below. After doing this I'd like to update wp_term_taxonomy.count . I set it to 0 (dumb?) so the new terms are not appearing in search forms (they do appear on the individual custom post though). Setting count to 1 fixes this but still 'wrong' and I'm worried this could cause issues down the line.

Answers within SQL or Wordpress will work though my coding is just above n00b status.

My solution: set wp_term_taxonomy.count to 1 for new terms so it'll appear across the site. I can use them in searches (important) but things like tag clouds are wrong/mis-sized (fine) and I'm worried I'll run into unknown issues.

Details: I am adding many terms to a taxonomy I have already created called 'csf_brand' for a custom post type called 'jeans'. Rather than editing each of several hundred custom posts already created I thought I'd batch them with SQL, using the 3 tables related to custom taxonomies.

First I create my new terms.

INSERT INTO `wp_nhyb_terms` (`term_id`, `name`, `slug`, `term_group`) VALUES
(10, 'GAP', 'gap', 0); --there's a bunch more lines like this adding in new terms

Next, I attach my terms to the appropriate taxonomy. 0 is not the right count but calculating this is not something I'm interested in doing if I can avoid it.

INSERT INTO `wp_nhyb_term_taxonomy` (`term_taxonomy_id`, `term_id`, `taxonomy`, `description`, `parent`, `count`) VALUES
(10, 10, 'csf_brand', '', 0, 0); --Here is where I could use 1 instead of 0

Finally, I attach the terms to my custom post types by the post ID (object_id)

INSERT INTO `wp_nhyb_term_relationships` (`object_id`, `term_taxonomy_id`, `term_order`) VALUES
(125, 10, 0);

And that works. But wp_term_taxonomy.count is not 0 so they aren't appearing in my searches (e.g. I have a page that can search for brands via dropdown and they aren't an option). If I edit a custom post in my browser then save it recognizes the new term and corrects the count. But that's not a great process since I'll eventually miss something.

  • Have you considered wp_insert_term an wp_set_object_terms? There's no need to use raw SQL to set create and change terms – Tom J Nowell Jun 29 '17 at 0:17
0

You can just run the wp_update_term_count_now php function after doing this:

https://developer.wordpress.org/reference/functions/wp_update_term_count_now/

Which calls the private _update_post_term_count

https://developer.wordpress.org/reference/functions/_update_post_term_count/

If you look at the source of that you will see how term counts are updated (which is done when you save a post on backend):

function _update_post_term_count( $terms, $taxonomy ) {
    global $wpdb;

    $object_types = (array) $taxonomy->object_type;

    foreach ( $object_types as &$object_type )
        list( $object_type ) = explode( ':', $object_type );

    $object_types = array_unique( $object_types );

    if ( false !== ( $check_attachments = array_search( 'attachment', $object_types ) ) ) {
        unset( $object_types[ $check_attachments ] );
        $check_attachments = true;
    }

    if ( $object_types )
        $object_types = esc_sql( array_filter( $object_types, 'post_type_exists' ) );

    foreach ( (array) $terms as $term ) {
        $count = 0;

        // Attachments can be 'inherit' status, we need to base count off the parent's status if so.
        if ( $check_attachments )
            $count += (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM $wpdb->term_relationships, $wpdb->posts p1 WHERE p1.ID = $wpdb->term_relationships.object_id AND ( post_status = 'publish' OR ( post_status = 'inherit' AND post_parent > 0 AND ( SELECT post_status FROM $wpdb->posts WHERE ID = p1.post_parent ) = 'publish' ) ) AND post_type = 'attachment' AND term_taxonomy_id = %d", $term ) );

        if ( $object_types )
            $count += (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM $wpdb->term_relationships, $wpdb->posts WHERE $wpdb->posts.ID = $wpdb->term_relationships.object_id AND post_status = 'publish' AND post_type IN ('" . implode("', '", $object_types ) . "') AND term_taxonomy_id = %d", $term ) );

        /** This action is documented in wp-includes/taxonomy.php */
        do_action( 'edit_term_taxonomy', $term, $taxonomy->name );
        $wpdb->update( $wpdb->term_taxonomy, compact( 'count' ), array( 'term_taxonomy_id' => $term ) );

        /** This action is documented in wp-includes/taxonomy.php */
        do_action( 'edited_term_taxonomy', $term, $taxonomy->name );
    }
}
0

The solution is simple, use the APIs given instead of using raw SQL

To create a new term:

wp_insert_term

To add or set terms on an object/post/user/etc:

wp_set_object_terms

There's also a synonym wp_set_post_terms but it's just a thin wrapper around it. There's no need to write raw SQL in WordPress if you're using standard data types

  • Thanks Tom. Your approach is better for most users. I'm going to leave sMyles as the accepted answer because it directly answers my question, where yours suggests a better alternative to my original problem. – Tom Jun 29 '17 at 16:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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