Get Orders Count by product ID every day in Woocommerce

user_3104331 0
Viewed 554

I want to get the table that represents two columns: order date and order count by specified product ID and order status. So, I should get how many orders were created in a specified date range by the product (by ID) with a specified status, for example, on-hold.

I've tried to combine several codes


SELECT DISTINCT count(p.ID) FROM wp_posts as p
WHERE p.post_type = 'shop_order' AND p.post_date BETWEEN '2021-01-01' AND '2021-01-21'
AND p.post_status IN ('wc-on-hold')

and


SELECT order_items.order_id
FROM wp_woocommerce_order_items as order_items
LEFT JOIN wp_woocommerce_order_itemmeta as order_item_meta ON order_items.order_item_id = order_item_meta.order_item_id
LEFT JOIN wp_posts AS posts ON order_items.order_id = posts.ID
WHERE posts.post_type = 'shop_order'
AND posts.post_status IN ('wc-on-hold')
AND order_items.order_item_type = 'line_item'
AND order_item_meta.meta_key = '_product_id'
AND order_item_meta.meta_value = '585'

but can't get the result I want.

I'm looking for a solution. Any suggestions?

orders woocommerce wordpress sql php
1 Answers

The following function using WordPress WPDB Class, will make a custom SQL query to get orders count based on a specific product ID, for a specific order status and for a given day:

get_order_count_by( $product_id, $status, $day )
    global $wpdb;

    $date_from = date( 'Y-m-d H:i:s', strtotime( $day ) );
    $date_to   = date( 'Y-m-d H:i:s', strtotime( $day . ' + 1 day' ) );

    return $wpdb->get_var( $wpdb->prepare("
        SELECT DISTINCT count(o.ID)
        FROM {$wpdb->prefix}posts o
        INNER JOIN {$wpdb->prefix}woocommerce_order_items oi
            ON o.ID = oi.order_id
        INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta oim
            ON oi.order_item_id = oim.order_item_id
        WHERE o.post_status = '%s'
            AND o.post_date >= '%s'
            AND o.post_date < '%s'
            AND oim.meta_key IN ('_product_id','_variation_id')
            AND oim.meta_value = %d
    ", $status, $date_from, $date_to, $product_id ) );
}

Code goes in functions.php file of the active child theme (or active theme). Tested and works.

USAGE EXAMPLE:

<?php echo '<span class="orders_count">' . get_order_count_by( 37, 'wc-processing', '2021-01-10' ) . '<span>'; ?>
user_37307540
Related