I am syncing the whole products database from an ORACLE database to a WooCommerce one.

To achieve this I am using kettle. My problem is: I need to update the product count when I finish adding the products and linking them to categories.

I can see two possibilities:

  • Run a PHP Script to update the count periodically (cron), something like this:

    function update_category_counters( $update_taxonomy = 'product_cat' ) {

    $get_terms_args = array(
        'taxonomy'   => $update_taxonomy,
        'fields'     => 'ids',
        'hide_empty' => false,
    $update_terms = get_terms( $get_terms_args );
    wp_update_term_count_now( $update_terms, $update_taxonomy );


  • Find a way to do all the things the function above does;

Here is what I've tried:

CREATE DEFINER=`xxxx`@`%` PROCEDURE `xxxx`()

delete from wp_options where option_name = 'product_cat_children';    

UPDATE wp_term_taxonomy SET count = (
    SELECT COUNT(*) FROM wp_term_relationships rel 
    LEFT JOIN wp_posts po ON po.ID = rel.object_id
    rel.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id 
    wp_term_taxonomy.taxonomy NOT IN ('link_category')
    po.post_status IN ('publish', 'future')

As seen here so_18669256.

Using this approach, I am able to list the categories in the front-end, but I can't open the admin's side product listing (Fatal error: Allowed memory size of 1572864000 bytes exhausted (tried to allocate 1495295696 bytes) in [...]/wp-content/plugins/woocommerce/includes/walkers/class-product-cat-dropdown-walker.php on line 66).

Is it possible to set All needed fields (when updating categories counters for products) with a stored procedure? If so, how?

Thanks in advance!

  • How about reducing the size of your query & have it run every 5 mins or so? – Nick_O Jun 7 '17 at 20:03
  • Products are added correctly, the problem is when I open an admin page to list WC's products... – Bruno Rodrigues Jun 7 '17 at 20:07
  • If I'm understanding correctly you are loading all of your products on one page? Can you implement pageination? – Nick_O Jun 7 '17 at 20:09
  • The page is built in wp-admin/edit.php?post_type=product – Bruno Rodrigues Jun 7 '17 at 20:32
  • Look under "screen options" and cap the number of products per page at a lower number, say 20. – WebElaine Jun 7 '17 at 21:54

Your Answer

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

Browse other questions tagged or ask your own question.