How to get orders that contains a product by date?

Question

I want to get the orders that contains a specific product ID by date. But the result is empty w<hile it should'nt. Ideally i need to send only the MONTH in $order_date to get orders by MONTH

function get_orders_by_product($product_id, $order_date) {
      
         global $wpdb;
         $order_status = ['wc-completed'];
       
         $results = $wpdb->get_col("
             SELECT order_items.order_id
             FROM {$wpdb->prefix}woocommerce_order_items as order_items
             LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta as order_item_meta ON order_items.order_item_id = order_item_meta.order_item_id
             LEFT JOIN {$wpdb->posts} AS posts ON order_items.order_id = posts.ID
             WHERE posts.post_type = 'shop_order'
             AND posts.post_status IN ( '" . implode( "','", $order_status ) . "' )
             AND posts.post_date = $order_date
             AND order_items.order_item_type = 'line_item'
             AND order_item_meta.meta_key = '_product_id'
             AND order_item_meta.meta_value = '".$product_id."'
             ORDER BY order_items.order_id DESC");
         return $results;
     }
    get_orders_by_product(19361,'2023-02-28');

Answer 1

The issue here is that you're trying to match the post_date exactly with $order_date. However, post_date is a datetime field and includes the time as well, so it's unlikely to match exactly with a date string like '2023-02-28'.

If you want to get orders by month, you can use the MySQL MONTH() and YEAR() functions to extract the month and year from the post_date and compare it with the month and year of $order_date. Here's how you can modify your function:

function get_orders_by_product($product_id, $order_date) {
      
    global $wpdb;
    $order_status = ['wc-completed'];

    // Extract the month and year from $order_date
    $order_month = date('m', strtotime($order_date));
    $order_year = date('Y', strtotime($order_date));

    $results = $wpdb->get_col("
        SELECT order_items.order_id
        FROM {$wpdb->prefix}woocommerce_order_items as order_items
        LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta as order_item_meta ON order_items.order_item_id = order_item_meta.order_item_id
        LEFT JOIN {$wpdb->posts} AS posts ON order_items.order_id = posts.ID
        WHERE posts.post_type = 'shop_order'
        AND posts.post_status IN ( '" . implode( "','", $order_status ) . "' )
        AND MONTH(posts.post_date) = $order_month
        AND YEAR(posts.post_date) = $order_year
        AND order_items.order_item_type = 'line_item'
        AND order_item_meta.meta_key = '_product_id'
        AND order_item_meta.meta_value = '".$product_id."'
        ORDER BY order_items.order_id DESC");
    return $results;
}
get_orders_by_product(19361,'2023-02-28');