Count number of orders for a variation in Woocommerce

Question

I have figured a way to do this, but I am finding my queries are taking way too long, and the more orders in Woocmmerce, and the more variations we add, the longer the query takes...

I would hope there is a way in WC or WP to query just variation ids of an order, but alas, I havent found it yet. I need to do sales report by variation.

//get number of orders per variation_id
function getOrdersfromVariation($variation_id){
    $numberOfOrders = 0;
    ip_write_log("getOrdersfromVariation varid: $variation_id");

    // rewrite with wc_get_orders
    $args = array(
      'status' => array(  'processing', 'completed'),
        'limit' => -1,
    );
    $orders = wc_get_orders( $args );
    if(isset($orders)){
    //TODO: Get order count - $total_orders = $orders->total;

       foreach ($orders as $order){
           foreach ($order->get_items() as $key => $lineItem) {
               $item_data = $lineItem->get_data();

               if ($item_data['variation_id'] == $variation_id) {
                  $numberOfOrders++;

               }
           }
        }
        if(isset($numberOfOrders)){
           return $numberOfOrders;
        }
     }
  return;
}

Answer 1

You can get the count of orders from specific orders status from a variation ID with this very light SQL query embedded in the following function:

function count_orders_from_variation($variation_id){
    global $wpdb;

    // DEFINE below your orders statuses
    $statuses = array('wc-completed', 'wc-processing');

    $statuses = implode("','", $statuses);

    return $wpdb->get_var("
        SELECT count(p.ID) FROM {$wpdb->prefix}woocommerce_order_items AS woi
        JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS woim ON woi.order_item_id = woim.order_item_id
        JOIN {$wpdb->prefix}posts AS p ON woi.order_id = p.ID
        WHERE p.post_type = 'shop_order' AND p.post_status IN ('$statuses')
        AND woim.meta_key LIKE '_variation_id' AND woim.meta_value = $variation_id
    ");
}

Code goes in function.php file of your active child theme (or active theme). Tested and Works.

USAGE EXAMPLE (Displaying orders count for variation ID 41):

echo '<p>Orders count: ' . count_orders_from_variation(41) . '</p>';