WooCommerce Query to return list of products and total orders placed for each product

Question

I am trying to get a list of all my WooCommerce products with the number of orders placed for them in the last year.

I tried to do this inside PHP, but was not successful as the "wc_get_orders" function does not offer such functionality.

So, I am trying in MySQL and have tried the following. In theory, it does work, but it takes a ridiculously long time to run.

I have 623 Products (Ideally, I would also like to get a Total Sales Value along side the Total Order Count, but that is not as important)

Can someone help? If someone has a PHP solution I am open to that as well

SELECT   posts.ID AS prodID, 
(
    SELECT COUNT( DISTINCT  order_items.order_id)
        FROM woocommerce_order_items as order_items
        LEFT JOIN woocommerce_order_itemmeta as order_item_meta ON order_items.order_item_id = order_item_meta.order_item_id
        LEFT JOIN posts AS posts ON order_items.order_id = posts.ID
        WHERE posts.post_type = 'shop_order' AND posts.post_date >= curdate() - interval 1 YEAR 
        AND order_items.order_item_type = 'line_item'
        AND order_item_meta.meta_key = '_product_id'
        AND (posts.post_status = 'wc-delivered' OR posts.post_status = 'wc-completed')
        AND order_item_meta.meta_value = prodID
)
            FROM posts  INNER JOIN postmeta ON ( posts.ID = postmeta.post_id )  INNER JOIN postmeta AS mt1 ON ( posts.ID = mt1.post_id )
            WHERE 1=1  AND ( 
  postmeta.meta_key = '_price' 
--  AND   ( mt1.meta_key = 'availibility' AND mt1.meta_value = 'Available' )
) AND posts.post_type = 'product' AND ((posts.post_status = 'publish'))
            GROUP BY posts.ID
            ORDER BY posts.post_date DESC

I have the following running in PHP which gives me my list of Products, but when I try to run a query on each iteration for the number of Orders, that is also very slow:

$args = array(
    'post_type' => 'product', // Replace 'product' with the actual post type name of your products
    'posts_per_page' => -1, // Retrieve all posts
    'post_status' => 'publish',
    'fields' => 'ids', // Retrieve only post IDs for performance optimization
    'meta_query' => array(
        'relation' => 'AND',
        array(
            'key' => '_price', // Replace '_price' with the actual meta key for price
            'compare' => 'EXISTS' // Make sure the price meta exists
        )
    )
);


$products = new WP_Query( $args );

Answer 1

How long is a "ridiculously long time to run"? Have you used a plugin like Index WP MySQL For Speed to add indices to the database?

I would have added this as a comment, as opposed to an answer, but it is a bit too big. Try the following query to see if it performs any better:

SELECT pr.ID AS prodID, IFNULL(po.num_orders, 0) AS num_orders
FROM posts pr -- pr for product
LEFT JOIN (
    SELECT oim.meta_value AS product_id, COUNT(o.ID) AS num_orders
    FROM posts o -- o for order
    JOIN woocommerce_order_items oi
        ON o.ID = oi.order_id
        AND oi.order_item_type = 'line_item'
    JOIN woocommerce_order_itemmeta oim
        ON oi.order_item_id = oim.order_item_id
        AND oim.meta_key = '_product_id'
    WHERE o.post_type = 'shop_order'
    AND o.post_date >= CURRENT_DATE - INTERVAL 1 YEAR 
    AND o.post_status IN ('wc-delivered', 'wc-completed')
    GROUP BY oim.meta_value
) po /* po for product_orders */ ON pr.ID = po.product_id
WHERE pr.post_type = 'product'
AND pr.post_status = 'publish'
-- AND EXISTS (SELECT 1 FROM postmeta WHERE post_id = pr.ID AND meta_key = 'availibility' AND meta_value = 'Available')
ORDER BY pr.post_date DESC;

You should get better performance querying wc_order_* tables instead of the woocommerce_order_* and posts tables. You could try something like:

SELECT
    pr.ID AS prodID,
    IFNULL(po.num_orders, 0) AS num_orders,
    IFNULL(po.total_units, 0) AS total_units,
    IFNULL(po.total_net, 0) AS total_net,
    IFNULL(po.total_gross, 0) AS total_gross
FROM posts pr
LEFT JOIN (
    SELECT
        op.product_id,
        COUNT(op.order_id) AS num_orders,
        SUM(op.product_qty) AS total_units,
        SUM(op.product_net_revenue) AS total_net,
        SUM(op.product_gross_revenue) AS total_gross
    FROM wc_order_stats o
    JOIN wc_order_product_lookup op
        ON o.order_id = op.order_id
    WHERE o.date_completed >= CURRENT_DATE - INTERVAL 1 YEAR 
    AND o.status IN ('wc-delivered', 'wc-completed')
    GROUP BY op.product_id
) po
    ON pr.ID = po.product_id
WHERE pr.post_type = 'product'
AND pr.post_status = 'publish'
-- AND EXISTS (SELECT 1 FROM postmeta WHERE post_id = pr.ID AND meta_key = 'availibility' AND meta_value = 'Available')
ORDER BY pr.post_date DESC;

If this is no better, please update your question with the EXPLAIN output for these queries and your query.