How to Use WC_Order_Query with a Custom Product Type and Date Range: Overcoming the Limitations
As a WordPress developer working with WooCommerce, you may have encountered a scenario where you need to query orders based on a custom product type and a specific date range. The standard WC_Order_Query
class provides a convenient way to perform such queries, but it may not always meet your requirements, especially when dealing with custom product types.
In this blog post, we'll dive into the limitations of using WC_Order_Query
with a custom product type and a date range, and we'll explore a more powerful solution using a custom SQL query to target specific product types in order items.
The Problem with WC_Order_Query
and Custom Product Types
The WC_Order_Query
class is a powerful tool provided by WooCommerce to query orders based on various criteria, such as status, customer, and metadata. However, when it comes to querying orders based on a custom product type, the class falls short.
Let's say you have a custom product type called "lottery" in your WooCommerce store. You want to query orders that contain this specific product type within a given date range. The natural approach would be to use the WC_Order_Query
class with the meta_key
parameter set to '_lottery'
.
$order_query = new WC_Order_Query( array(
'meta_key' => '_lottery',
'date_query' => array(
array(
'after' => '-7 days',
'before' => 'today',
'inclusive' => true,
),
),
) );
$orders = $order_query->get_orders();
However, this approach will not work as expected, and you'll encounter the following error:
What fails is 'meta_key' => '_lottery'.
Why?
Because you are querying Orders but NOT products meta data (order items).
As "lottery" is a custom product type that has nothing to do with the order meta data, it fails.
The issue here is that the WC_Order_Query
class is designed to query the order metadata, not the product metadata. The '_lottery'
meta key does not exist in the order data; it's a custom field associated with the product itself.
A More Powerful Solution: Using a Custom SQL Query
To overcome the limitations of WC_Order_Query
when dealing with custom product types, we can use a more powerful approach: a custom SQL query that directly targets the order items and their associated product data.
Here's an example of a SQL query that will retrieve the order IDs based on a custom product type and a date range:
global $wpdb;
$product_type = 'lottery'; // The product type to target
// Dates
$currentDayOfWeek = date("N");
$time_start = strtotime(date('Y-m-d', strtotime('-'.(intval($currentDayOfWeek )-1).' days')));
$time_end = strtotime(date('Y-m-d', strtotime('+'.(7 - intval($currentDayOfWeek )).' days')));
// The SQL query
$order_ids = $wpdb->get_col( "
SELECT DISTINCT pm.post_id
FROM {$wpdb->prefix}postmeta as pm
INNER JOIN {$wpdb->prefix}woocommerce_order_items as woi ON pm.post_id = woi.order_id
INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta as woim ON woi.order_item_id = woim.order_item_id
INNER JOIN {$wpdb->prefix}term_relationships as tr ON woim.meta_value = tr.object_id
INNER JOIN {$wpdb->prefix}term_taxonomy as tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
INNER JOIN {$wpdb->prefix}terms as t ON tt.term_id = t.term_id
WHERE pm.meta_key LIKE '_date_paid'
AND pm.meta_value BETWEEN '$time_start' AND '$time_end'
AND woi.order_item_type LIKE 'line_item'
AND woim.meta_key LIKE '_product_id'
AND tt.taxonomy LIKE 'product_type'
AND t.slug LIKE '$product_type'
ORDER BY pm.meta_value DESC
" );
// Raw output display (testing)
echo '<pre>'; print_r($order_ids); echo '</pre>';
Let's break down the query:
- We start by getting the current day of the week and calculating the start and end dates for the previous 7 days.
- The SQL query uses multiple
JOIN
statements to link the postmeta
, woocommerce_order_items
, woocommerce_order_itemmeta
, term_relationships
, term_taxonomy
, and terms
tables. This allows us to access the order metadata, order items, and the associated product types.
- The
WHERE
clause filters the results to include only orders with a '_date_paid'
meta key that falls within the specified date range, and where the order item's product type matches the custom "lottery" product type.
- The
ORDER BY
clause sorts the results by the order's paid date in descending order.
The output of this query will be an array of order IDs that match the specified criteria.
Advantages of the Custom SQL Approach
The custom SQL query approach has several advantages over using the WC_Order_Query
class:
- Flexibility: The SQL query allows you to target specific product types in the order items, which is not possible with the
WC_Order_Query
class.
- Performance: By directly querying the database tables, the custom SQL query can be more efficient than the abstraction provided by the
WC_Order_Query
class, especially for complex queries.
- Extensibility: The SQL query can be easily modified to include additional filters or sorting options as per your requirements.
Integrating the Custom SQL Query into Your Code
To use the custom SQL query in your code, you can replace the WC_Order_Query
usage with the following:
global $wpdb;
$product_type = 'lottery'; // The product type to target
// Dates
$currentDayOfWeek = date("N");
$time_start = strtotime(date('Y-m-d', strtotime('-'.(intval($currentDayOfWeek )-1).' days')));
$time_end = strtotime(date('Y-m-d', strtotime('+'.(7 - intval($currentDayOfWeek )).' days')));
// The SQL query
$order_ids = $wpdb->get_col( "
SELECT DISTINCT pm.post_id
FROM {$wpdb->prefix}postmeta as pm
INNER JOIN {$wpdb->prefix}woocommerce_order_items as woi ON pm.post_id = woi.order_id
INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta as woim ON woi.order_item_id = woim.order_item_id
INNER JOIN {$wpdb->prefix}term_relationships as tr ON woim.meta_value = tr.object_id
INNER JOIN {$wpdb->prefix}term_taxonomy as tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
INNER JOIN {$wpdb->prefix}terms as t ON tt.term_id = t.term_id
WHERE pm.meta_key LIKE '_date_paid'
AND pm.meta_value BETWEEN '$time_start' AND '$time_end'
AND woi.order_item_type LIKE 'line_item'
AND woim.meta_key LIKE '_product_id'
AND tt.taxonomy LIKE 'product_type'
AND t.slug LIKE '$product_type'
ORDER BY pm.meta_value DESC
" );
// Raw output display (testing)
echo '<pre>'; print_r($order_ids); echo '</pre>';
This code will retrieve an array of order IDs that match the specified custom product type and date range. You can then use this array to perform further operations, such as fetching the actual order objects or generating reports.
In conclusion, while the WC_Order_Query
class is a powerful tool for querying orders in WooCommerce, it may fall short when dealing with custom product types. By using a custom SQL query, you can overcome these limitations and target specific product types in order items, leading to more precise and efficient order management in your WooCommerce-powered website.
Flowpoint.ai can help you identify all the technical errors that are impacting conversion rates on your website and directly generate recommendations to fix them.
Get a Free AI Website Audit
Automatically identify UX and content issues affecting your conversion rates with Flowpoint's comprehensive AI-driven website audit.