Get WooCommerce orders by coupon code and date range with a SQL Query

Question

I am trying to get order data by coupon code and date. I have added this code. But how to find the data by coupon code and date?

$date_from = '2015-11-20';
$date_to = '2015-12-20';
$post_status = implode("','", array('wc-processing', 'wc-completed') );

$result = $wpdb->get_results( "SELECT * FROM $wpdb->posts 
            WHERE post_type = 'shop_order'
            AND post_status IN ('{$post_status}')
            AND post_date BETWEEN '{$date_from}  00:00:00' AND '{$date_to} 23:59:59'
        ");

echo "<pre>";
print_r($result);

See about sql injection and the importance of prepared and bound queries. But first, let's focus on the query. For that, see https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query — Strawberry, Mar 08 '21 at 08:37

score 1 · Accepted Answer · answered Mar 08 '21 at 12:07

The following custom function, will get WooCommerce processing and completed orders Ids by coupon code and date range using a custom SQL Query:

/*
 * Get WooCommerce orders Ids by coupon code and date range
 * 
 * @param string $coupon_code The coupon code
 * @param string $date_from The starting date (format 'Y-m-d')
 * @param string $date_to The end date (format 'Y-m-d')
 * @param array  $statuses The order statuses (optional | Default "processing" and "completed"
 *
**/
function get_order_ids_by_coupon_and_date_range( $coupon_code, $date_from, $date_to, $statuses = array() ){
    // Default order statuses set to 'processing' and 'completed'
    $statuses = empty($statuses) ? array('processing', 'completed') : $statuses;

    global $wpdb;

    return $wpdb->get_col( $wpdb->prepare("
        SELECT p.ID
        FROM $wpdb->posts p
        INNER JOIN {$wpdb->prefix}woocommerce_order_items oi
            ON p.ID = oi.order_id
        WHERE p.post_type = 'shop_order'
            AND p.post_status IN ('wc-" . implode("','wc-", $statuses )."')
            AND p.post_date BETWEEN '%s' AND '%s'
            AND oi.order_item_type = 'coupon'
            AND oi.order_item_name = '%s'
    ", $date_from, $date_to, sanitize_title( $coupon_code ) ) );
}

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

Usage example:

$coupon_code = 'special10';
$date_from   = '2021-02-01';
$date_to     = '2020-03-01';

$orders_ids  = get_order_ids_by_coupon_and_date_range( $coupon_code, $date_from, $date_to );

echo '<pre>' . print_r( $orders_ids, true ) . '</pre>';

Get WooCommerce orders by coupon code and date range with a SQL Query

1 Answers1