How to get best selling product category IDs in WooCommerce?

Question

I looking for way to display the 6 best selling product categories. this is what i got now:

$termsprent = get_terms(
    array(
        'taxonomy'    => 'product_cat',
        'hide_empty'  => true,
        'numberposts' => 4,
        'meta_key'    => 'total_sales',
        'orderby'     => 'meta_value_num',
        'order'       => 'desc',
    )
);

does anyone knows a way to modify it so it will display the top selling product categories?

Answer 1

A slightly modified and extended version of @vincenzo-di-gaetano answer.

Function immediately returns the array of WP_Terms. As for me, it is more convenient, although the object is modified. Result will be like:

array(2) {
  [0]=> object(WP_Term)#3977 (11) { 
    ["term_id"]=> int(15)
    ["name"]=> string(13) "Uncategorized"
    ...
    ["sales"]=> string(1) "5" 
  } 
  [1]=> object(WP_Term)#3978 (11) { 
    ["term_id"]=> int(16)
    ["name"]=> string(13) "Uncategorized 2"
    ...
    ["sales"]=> string(1) "3" 
  } 
}

function popular_product_categories( $limit = null ): array {
    $terms = array();
    $product_categories = get_terms( 'product_cat' );
    foreach ( $product_categories as $product_cat ) {
        // set total sales property
        $product_cat->sales = counts_total_sales_by_product_category( $product_cat->term_id );
        $terms[$product_cat->term_id] = $product_cat;
    }

    // filter out empty terms
    $terms = array_filter( $terms, fn($term) => $term->sales > 0 );

    // sort the terms by sales values in descending order
    usort($terms, fn($a, $b) => $a->sales <=> $b->sales);

    return array_slice( $terms, 0, $limit, true );
}

Caching is also appropriate for such operations, so I added it to the example:

// Return total sales number for a product category
function counts_total_sales_by_product_category( $term_id, $use_cache = true ): ?string {
    global $wpdb;
    $cache = $use_cache ? get_transient( 'total_sales_term_'.$term_id ) : false;
    
    // Check if we can return value early
    if ( false !== $cache ) {
        return $cache;
    }
    $total_sales = $wpdb->get_var("
        SELECT sum(meta_value)
        FROM $wpdb->postmeta
        INNER JOIN {$wpdb->term_relationships} ON ( {$wpdb->term_relationships}.object_id = {$wpdb->postmeta}.post_id )
        WHERE ( {$wpdb->term_relationships}.term_taxonomy_id IN ($term_id) )
        AND {$wpdb->postmeta}.meta_key = 'total_sales'"
    );
    // Update cache value
    set_transient( 'total_sales_term_'.$term_id, $total_sales, 12 * HOUR_IN_SECONDS );
    return $total_sales;
}

Additionally, we can update cache when the order is completed

// Update counts_total_sales_by_product_category transient when a product is sold
function update_total_sales_term_transient( $order_id ): void {
    $order = wc_get_order( $order_id );
    $items = $order->get_items();

    foreach ( $items as $item ) {
        $product_id = $item->get_product_id();
        $product_categories = get_the_terms( $product_id, 'product_cat' );
        if ( ! empty( $product_categories ) ) {
            // recalculate total sales for each category
            foreach ( $product_categories as $product_category ) {
                counts_total_sales_by_product_category( $product_category->term_id, false); // use the second parameter to override the cache
            }
        }
    }
}
add_action('woocommerce_order_status_completed', 'update_total_sales_term_transient');

Answer 2

GETS THE TOTAL SUM OF SALES OF A PRODUCT CATEGORY

You can use the following function to get the total sum of sales of products belonging to a specific product category.

The only parameter of the function is the product category ID (term_id)

// gets the total sales count of a specific product category
function counts_total_sales_by_product_category( $term_id ) {
    global $wpdb;
    $total_sales = $wpdb->get_var("
        SELECT sum(meta_value)
        FROM $wpdb->postmeta
        INNER JOIN {$wpdb->term_relationships} ON ( {$wpdb->term_relationships}.object_id = {$wpdb->postmeta}.post_id )
        WHERE ( {$wpdb->term_relationships}.term_taxonomy_id IN ($term_id) )
        AND {$wpdb->postmeta}.meta_key = 'total_sales'"
    );
    return $total_sales;
}

GET THE BEST SELLING PRODUCT CATEGORIES

The following function will return an array with the best selling product categories sorted in descending order.

The only parameter of the function is the limit of product categories to be returned.

// gets the n product categories with the best sales
function gets_best_selling_product_categories( $limit ) {

    $total_sales = array();
    $product_categories = get_terms( 'product_cat' );
    foreach ( $product_categories as $product_cat ) {
        $product_cat_id = $product_cat->term_id;
        $total_sales[$product_cat_id] = counts_total_sales_by_product_category( $product_cat_id );
    }

    // removes empty values from the array
    $total_sales = array_filter( $total_sales );

    // sorts the array values in descending order
    arsort( $total_sales );

    // gets the first n ($limit) product categories with the most sales
    $best_product_categories = array_slice( $total_sales, 0, $limit, true );

    return $best_product_categories;
}

RESULT

Below is the result of the var_dump() of the gets_best_selling_product_categories( 4 ); function:

array(4) {
    [15]=> string(3) "209"
    [30]=> string(3) "160"
    [32]=> string(2) "56"
    [31]=> string(2) "18"
}

The array key will be the product category id and its value is the total sum of sales for that category.

The code has been tested and works. Add it to your active theme's functions.php.