How to Find Purchased, Returned, and Owned Product Quantities

You can implement a lot of creative experiences for your WooCommerce customers by knowing the exact quantities of each product a customer has purchased, returned, and currently owns. For instance, you could set purchasing benchmark rewards for your customers to unlock special discounts and content.

In addition to improving your customers' experiences, you could also generate some noteworthy reports to then take action on. If you find that too many returns are being issued, for example, then you could easily limit the quantity of returns per product that can be issued to each customer.

My immediate need for this function was for a simple content protection plugin I am currently developing. I wanted to protect content by the products a user currently owns. When searching for a WooCommerce function to do this, I was directed to their wc_customer_bought_product() function. Pretty quickly, I found that it was not the right tool for the job, so I developed my own function, found at the end of this article.

Problems with wc_customer_bought_product()

This WooCommerce function should definitely not be used for checking if a customer owns a product. Despite its intended usage, too, I'd easily argue that it shouldn't even be used for what it claims! Which, by the way, is in version 2.2.0 of the WooCommerce Customer Functions file as I'm writing this. Let me clarify each of the issues I noticed with this function.

Slower with Stale Data

For starters, wc_customer_bought_product() uses a 30-day transient to reduce database calls. While I agree with optimization, 30 days is a very long time to reflect changes in product ownership. Customers should immediately be rewarded for purchasing a new product! Yes, I could change it to a 1-day transient or even a 1-hour transient, but that negligible amount of efficiency certainly isn't worth the mess of transient management.

Doing a single variable query to our own database is so trivial that managing transients could even cause more overhead! This is even more likely to be the case because they then use a query to select more than one result with $wpdb->get_col() and require the usage of in_array() (which has a linear complexity, O(n)) for every function call, regardless of the transient's validity.

Inaccurate Results

Second, this function uses wc_get_is_paid_statuses() which only returns 'processing' and 'completed' by default. This means that line items in refunded orders will be completely ignored by the query. More specifically, refunded line items where the order still has a status of 'completed' will be counted. Does this make any sense to you?

Let me explain it even further. If the line item was returned and it was the only item in the order, then the order's status will change to 'refunded' which means the returned product will not be seen as having been bought. If the line item was returned but was not the only item in the order, then the order's status will stay as 'processing' or 'completed' which means the returned product will be seen as having been bought.

Alright, I've ranted enough. Let's move on!

Less Information

The last problem I have with using wc_customer_bought_product() is actually just my personal preference, though a valid argument nonetheless.

wc_customer_bought_product() returns a boolean which really boils down the data. Returning the quantity of products that had been bought, in my opinion, is way more useful. If the returned integer is greater than 0, then what do you know, it has been bought. And if you're really just aching for a boolean instead, then you could quickly make a wrapper function to do the comparison for you. One and done!

function customer_bought_product( ... ) : bool {

  if ( get_customer_product_quantity( ... ) > 0 ) {
    return TRUE;
  }

  return FALSE;

}

Solution to Query Product Quantities by Customer

Finally! The chapter with less ranting and more solving! You can find the full function at the end of this post, but I'm going to take a moment to explain its details. This way, you'll be better able to understand how to use it and tweak it to suite your own needs.

Why Use One Function?

Look, almost a third (32.6%, actually) of this function's body is simply dealing with the parameters. I didn't want to be managing that mess in multiple places or creating a helper function just for that. As a bonus, I think the function reads a lot nicer this way rather than trying to remember 3 ridiculously long signatures!

Customer Query Parameters

Speaking of the lengthy parameter handling, let me explain. To support guest orders, you can search by user ID, user email, or both! Since it's common to just use the currently authenticated user, the function will use the current user's ID and email by default.

After verifying WooCommerce is available and validating the parameters, the user ID is set to the current user's ID if the parameter was left as the default value of -1. This means the user ID is now set as desired to continue.

Next, the user's ID and email will be added for query usage if a valid user ID is in use. If the user ID is 0 or less at this point in the code, then a WordPress user was not specified or currently logged in.

Finally, the additionally provided email is validated for query usage and the resulting array of customer query data is simplified. array_unique() ensures the same customer email was not added twice since this could happen if the passed email and current user's email were the same.

Paid Order Statuses

After ensuring data exists for the customer query, the paid order statuses are collected. Because wc_get_is_paid_statuses() only returns 'processing' and 'completed' by default, 'refunded' is also added because a customer had to have paid to be refunded. I'll refrain from going off on a tangent about this again like I did in the Inaccurate Results section!

Purchased Product Quantity Query

This query sums all shop_order order item _qty meta values. The query joins the following 4 tables to find the quantity of each relevant line item:

  1. posts is used to find the IDs of all shop_order posts with a paid status.
  2. postmeta helps determine which orders pertain to the desired customer by checking the _billing_email and _customer_user meta values.
  3. woocommerce_order_items provides the order item IDs for meta lookups.
  4. woocommerce_order_itemmeta determines which order items are the desired product and provides the purchased quantity. Since two meta entries need to be checked, _product_id/_variation_id and _qty, this table is joined twice.

Returned Product Quantity Query

To query the quantity of refunded line items, the same 4 tables are used. However, just one more posts table join is needed to find the IDs of all applicable refunds. The original order is set as the post_parent of the shop_order_refund posts that apply to them. This is how WooCommerce relates order refunds to original orders.

The Full Function

Now that I've written an entire college essay for you, take this function and get back to work! 😁 See, this is why I've only written at most 1 blog post each month thus far...

/**
 * Counts a customer's (by ID or email or both) total purchased, returned,
 * or owned quantity of a product. If a user ID is used in the search, then
 * that user's email will also be used in the search.
 *
 * @param int $product_id The product to count. Variation ID also works.
 *
 * @param string $qty_type Optional. The type of quantity to return:
 * 'purchased', 'returned', or 'owned'. Default 'owned'.
 *
 * @param int $user_id Optional. The customer to check. Set to 0 to not
 * search by a user ID. Default -1 to use the current user's ID.
 *
 * @param string $customer_email Optional. An additional customer email to
 * check. Default '' to only use the customer's user email.
 *
 * @return int The customer's product quantity. Returns -1 if an error occurred.
 */
function get_customer_product_quantity( int $product_id, string $qty_type = 'owned', int $user_id = -1, string $customer_email = '' ) : int {

  if (
    class_exists( 'WooCommerce' ) === FALSE
    || function_exists( 'wc_get_is_paid_statuses' ) === FALSE
  ) {
    return -1;
  }

  if ( $user_id < -1 ) {
    return -1;
  }

  if ( $product_id <= 0 ) {
    return -1;
  }

  if ( $user_id === -1 ) {
    $current_user = wp_get_current_user();
    $user_id = $current_user->ID;
  }

  $customer_data = [];

  if ( $user_id > 0 ) {
    $customer_data[] = $user_id;
    $user = get_user_by( 'id', $user_id );
    if ( isset( $user->user_email ) ) {
      $customer_data[] = $user->user_email;
    }
  }

  if ( ! empty( $customer_email ) ) {
    $customer_email = filter_var( $customer_email, FILTER_SANITIZE_EMAIL );
    if ( is_email( $customer_email ) ) {
      $customer_data[] = $customer_email;
    }
  }

  $customer_data = array_map( 'esc_sql', array_filter( array_unique( $customer_data ) ) );
  if ( count( $customer_data ) === 0 ) {
    return -1;
  }

  $order_statuses   = array_map( 'esc_sql', wc_get_is_paid_statuses() );
  $order_statuses[] = 'refunded';

  global $wpdb;

  /* Purchased Qty */

  if ( $qty_type !== 'returned' ) {

    $purchased_qty = $wpdb->get_var(
      "
      SELECT
          SUM(product_qty.meta_value)
      FROM
          {$wpdb->posts} AS p
      INNER JOIN {$wpdb->postmeta} AS pm
      ON
          p.ID = pm.post_id
      INNER JOIN {$wpdb->prefix}woocommerce_order_items AS i
      ON
          p.ID = i.order_id
      INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS im
      ON
          i.order_item_id = im.order_item_id
      INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS product_qty
      ON
          im.order_item_id = product_qty.order_item_id
      WHERE
          p.post_status IN ( 'wc-" . implode( "','wc-", $order_statuses ) . "' )
          AND p.post_type = 'shop_order'
          AND pm.meta_key IN ( '_billing_email', '_customer_user' )
          AND pm.meta_value IN ( '" . implode( "','", $customer_data ) . "' )
          AND im.meta_key IN( '_product_id', '_variation_id' )
          AND im.meta_value = {$product_id}
          AND product_qty.meta_key = '_qty'
      "
    ); // WPCS: unprepared SQL ok.

    if ( NULL === $purchased_qty ) {
      $purchased_qty = 0;
    }

    if ( $qty_type === 'purchased' ) {
      return (int) $purchased_qty;
    }

  }

  /* Returned Qty */

  $returned_qty = $wpdb->get_var(
    "
    SELECT
        SUM(refund_qty.meta_value)
    FROM
        {$wpdb->posts} AS p
    INNER JOIN {$wpdb->postmeta} AS pm
    ON
        p.ID = pm.post_id
    INNER JOIN {$wpdb->posts} AS refund_orders
    ON
        p.ID = refund_orders.post_parent
    INNER JOIN {$wpdb->prefix}woocommerce_order_items AS refund_items
    ON
        refund_orders.ID = refund_items.order_id
    INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS refund_itemmeta
    ON
        refund_items.order_item_id = refund_itemmeta.order_item_id
    INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS refund_qty
    ON
        refund_itemmeta.order_item_id = refund_qty.order_item_id
    WHERE
        p.post_status IN ( 'wc-" . implode( "','wc-", $order_statuses ) . "' )
        AND p.post_type = 'shop_order'
        AND pm.meta_key IN ( '_billing_email', '_customer_user' )
        AND pm.meta_value IN ( '" . implode( "','", $customer_data ) . "' )
        AND refund_itemmeta.meta_key IN ( '_product_id', '_variation_id' )
        AND refund_itemmeta.meta_value = {$product_id}
        AND refund_qty.meta_key = '_qty'
    "
  ); // WPCS: unprepared SQL ok.

  if ( NULL === $returned_qty ) {
    $returned_qty = 0;
  }

  if ( $qty_type === 'returned' ) {
    return (int) absint( $returned_qty );
  }

  return (int) $purchased_qty + $returned_qty;

}//end get_customer_product_qty()