Retrieving Customers Subscribed to a WooCommerce Product

On many of our clients’ ecommerce sites, we use the WooCommerce Subscriptions plugin to manage recurring products or memberships. In some cases we need to retrieve a list of all customers who are actively subscribed to a particular product.

Common Solution

The generally accepted solution for listing the customers is to retrieve a list of subscriptions and loop through each one to retrieve the customer.

It looks something like this.

This works fine for small sites with a couple hundred subscribers.

The Downside

Using the wcs_get_subscriptions_for_product runs a reasonably performant query to retrieve the subscription ids. To load the customer ids, it must loop through the results and call wcs_get_subscription on each one. Depending on your cache situation, this could mean at most a lot of queries, or at least a lot of cache server lookups.

Turns out that first retrieving the subscriptions to then retrieve the customers is not very efficient and will lead to performance degradation on large sites.

A Direct Approach

Instead of getting a list of subscriptions from the query, we can get a list of customers directly with some creative MySQL

The product id in this example is ‘123’ and must be changed to the actual product id

Let’s break down what is happening here.

1. We SELECT the user (customer) id from the users table.
2. We JOIN the orders table using the user id.
   • We limit the order types to shop_subscription only.
   • We limit subscriptions to active subscriptions only.
3. We JOIN the order_items table using the order id.
   • We limit order_items to only line_items as we don’t need any other information.
4. We JOIN the order_itemmeta table using the order item id.
   • We limit order_itemmeta items to _variation_id and _product_id only.
   • We limit the value of _variation_id or _product_id to the id of the product we are looking for.

The result is we get a list of user ids who are:

1. Subscribed to the provided product id.
2. Who have an active subscription.

Why the INNER JOIN

When working with a huge database, it is often more performant to use INNER JOIN to limit the resulting set before limiting the results based on criteria. The provided INNER JOIN replaces the typical WHERE statements to limit early and allow MySQL to further optimize the query.

Using INNER JOIN also reduces the resulting set when debugging queries use SELECT *.

If you prefer to use WHERE it will work perfectly fine.

Reusability

It is not reasonable to send a query directly to MySQL over and over throughout a codebase, so let’s put everything we have into a reusable function.

Notes

1. It is assumed your site has HPOS enabled.
2. You will probably want to add some caching if you’re going to be querying often.