[SQL] Count number of orders for a variation in Woocommerce

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;
}

Continue reading...