WooCommerce - Calculate Orders Count By Product Category

woocommerce-calculate-orders-count-by-product-category.php

/**
 * @snippet       Calculate Orders Count By Product Category
 * @author        Rasool Vahdati
 * @compatible    WooCommerce 7
 */

/**
 * Retrieve orders containing products from a specific category - Written by Rodolfo Melogli
 *
 * @param string $cat_slug       The slug of the product category.
 * @param array  $order_status   Optional. Array of order statuses to include. Default is an empty array, meaning all statuses will be included.
 * @return array                 An array of order IDs containing products from the specified category.
 */
function get_orders_by_product_cat( $cat_slug, $order_status = array() ) {
    global $wpdb;
    
    // Set default status to include all if empty array passed
    if ( empty( $order_status ) ) {
        $order_status = array( 'wc-completed', 'wc-processing', 'wc-on-hold', 'wc-pending', 'wc-cancelled', 'wc-refunded', 'wc-failed' );
    }
    
    $args = array(
        'limit' => -1,
        'status' => 'publish',
        'return' => 'ids',
        'category' => array( $cat_slug ),
    );
    $product_ids = wc_get_products( $args );
    $results = $wpdb->get_col("
        SELECT order_items.order_id
        FROM {$wpdb->prefix}woocommerce_order_items as order_items
        LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta as order_item_meta ON order_items.order_item_id = order_item_meta.order_item_id
        LEFT JOIN {$wpdb->posts} AS posts ON order_items.order_id = posts.ID
        WHERE posts.post_type = 'shop_order'
        AND posts.post_status IN ( '" . implode( "','", $order_status ) . "' )
        AND order_items.order_item_type = 'line_item'
        AND order_item_meta.meta_key = '_product_id'
        AND order_item_meta.meta_value IN ( '" . implode( "','", $product_ids ) . "' )
    ");
    return $results;
}

/**
 * Calculate the number of orders containing products from a specific category
 *
 * @param string $cat_slug   The slug of the product category.
 * @return int               The total number of orders containing products from the specified category.
 */
function cat_order_count( $cat_slug ) {
    $orders = get_orders_by_product_cat( $cat_slug );
    return count( $orders );
}

/**
 * Add a custom column to the product category admin page to display the total number of orders
 *
 * @param array $columns       An array of column headers.
 * @return array               An updated array of column headers including the custom column.
 */
function add_custom_column_header( $columns ) {
    $columns['order_count'] = __('Order Count');
    return $columns;
}

/**
 * Display the content of the custom column on the product category admin page
 *
 * @param string $content      The current content of the column.
 * @param string $column_name  The name of the current column.
 * @param int    $term_id      The ID of the current term.
 * @return string              The updated content of the column including the order count.
 */
function add_custom_column_content( $content, $column_name, $term_id ) {
    if ( 'order_count' === $column_name ) {
        $order_count = cat_order_count( get_term( $term_id )->slug );
        $content .= $order_count;
    }
    return $content;
}

add_filter( 'manage_edit-product_cat_columns', 'add_custom_column_header' );
add_filter( 'manage_product_cat_custom_column', 'add_custom_column_content', 10, 3 );