Today I noticed a question in the Advanced WooCommerce group on Facebook. Someone was asking a question about how to use the WC_Order_Query class to find subscriptions.
I am using wc_get_orders() to return a list of orders (seems reasonable!). But I’d like to exclude subscriptions. My current query uses type => ‘shop_order’, which returns all orders including subscriptions.
I haven’t yet found any method/property at the order level that exposes whether the order is for a subscription. Is querying and checking the products for each order my only option? WooCommerce Subscription docs have the following:
“All subscriptions are instantiated as an instance of a WC_Subscription. As subscriptions are a custom order type, this class extends the WC_Order and inherits all the methods of WC_Order (as well as WC_Abstract_Order).”
So, I am looking to query such that I DON’T include WC_Subscription entities. Unfortunately, the type property, as returned by get_type(), returns ‘shop_order’, even on subscriptions.
Any ideas?
So of course now I’m curious about how to do this myself and I did a little digging. What I came up with may not be the best solution but it does seem to work. I was disappointed to not find anything in the WooCommerce code, or the Subscriptions add-on code to help with this.
My reply: You could try the following for your use of WC_Order_Query. It’s not really pretty – it’s running a custom query as Sam mentions (which a taxonomy query added to the WC_Order_Query would also do). But I couldn’t find a way to easily modify the query that WC_Order_Query runs. Instead, I built a pre-query that finds all the orders IDs that include subscription items, using the ‘product_type’ taxonomy of ‘subscription’. It then adds these IDs to a ‘post__in’ argument for WC_Order_Query.
[php_tag]
/**
* @snippet WooCommerce Subscription Order Query
* @url https://davejesch.com/snippets/using-wc_order_query-to-find-subscriptions/
* @author Dave Jesch
* @date-written Feb 9 2020
* @testedwith WooCommerce 3.6.5
* @donate $5 http://davejesch.com/send-me-coffee/
*/
function d3j_get_non_subscription_orders($args)
{
$args['post__not_in'] = d3j_get_subscription_order_ids();
$query = new WC_Order_Query($args);
return $query;
}
function d3j_get_subscription_orders($args)
{
$args['post__in'] = d3j_get_subscription_order_ids();
$query = new WC_Order_Query($args);
return $query;
}
function d3j_get_subscription_order_ids()
{
global $wpdb
$sql = "SELECT `oi`.`order_id`
FROM `{$wpdb-[gt]prefix}woocommerce_order_items` `oi`
LEFT JOIN `{$wpdb-[gt]prefix}woocommerce_order_itemmeta` `im`
ON `im`.`meta_key` = '_product_id' AND `im`.`meta_id` = `oi`.`order_item_id`
LEFT JOIN `{$wpdb-[gt]prefix}term_relationships` `tr`
ON `tr`.`object_id` = `im`.`meta_value`
LEFT JOIN `{$wpdb-[gt]prefix}terms` AS `term`
ON `term`.`term_id` = `tr`.`term_taxonomy_id` AND `term`.`slug` = 'subscription'
WHERE `term`.`slug` IS NOT NULL";
$res = $wpdb-[gt]get_col($sql);
return $res;
}
The person asking was only looking for a way to exclude subscription orders. But the logic for including and excluding the orders is the same so I thought I’d provide functions to do both types of order queries.
Both functions use the d3j_get_subscription_order_ids() function. This is where the real work is done. I couldn’t find anything in the WooCommerce code that help you distinguish between a subscription and a non-subscription order. It doesn’t seem they though of this. I do know that the product type uses a taxonomy- the ‘product_type’ taxonomy. So I thought about just adding the taxonomy as part of the query being sent to WC_Order_Query. The problem is, the taxonomy is on the Product — and we’re querying Orders. Modifying the WC_Order_Query arguments to add the ‘subscription’ type won’t work.
My solution is to do a query that retrieves all of the Orders and do a JOIN to find the Products in the Orders, then do another JOIN to reduce those to the Products to just those that are referring to Subscription products.
This is probably not super fast. But consider this if you’re worried about running a subscription query before running the WC_Order_Query: When I tried simply adding the ‘product_type’ taxonomy query, the WP_Query code does essentially the same thing. It runs a query to find the taxonomy set, and then uses the results in the equivalent of another ‘post__in’ argument. So it’ll do two queries as well.
I did write the query to start on the woocommerce_order_items table. This should be a smaller table than the wp_posts table. So the query would run a bit faster than it would by starting with the wp_posts table.
I could have implemented this as some filters on the WP_Query that WC_Order_Query creates. I’ve done this before, but that means adding a bunch of JOINs and WHERE clauses onto the WP_Query before it’s run. While it works, its has the appearance of being a very difficult solution. And when working on more complex queries, such as those for WooCommerce Orders, it may cause other problems. This approach is unlikely to cause any unexpected behavior. So while it may not be the fastest way to accomplish this (one query is often a faster solution than two), I think it’s completely reliable for this use case.
If you try this out, please let me know. Especially if you run into any problems with it. I would also like to hear from you if you notice any performance issues with large numbers of products and subscriptions. SQL is great at working with large sets of data by itself but the subscription order query will add some overhead. Since I haven’t worked on any sites with large numbers of subscriptions I’d like some feedback on it.