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 which can be found at the end of this article.

Problems with wc_customer_bought_product()

As I tested wc_customer_bought_product(), I found this WooCommerce function should definitely not be used for checking if a customer currently owns a product. I also found that it shouldn't even be used to accurately check if a user has ever bought a specific product, the method's proclaimed usage. Let me clarify the two main reasons I decided to not use this function as I reviewed it in version 2.2.0 of the WooCommerce Customer Functions file.

Inaccurate Results

wc_customer_bought_product() uses wc_get_is_paid_statuses() which only returns 'processing' and 'completed' by default. This means that line items in refunded orders will be ignored by the query. More specifically, refunded line items where the order still has a status of 'completed' will be counted. Clearly, this function does not critically observe returned items.

Let me explain this discrepancy 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.

Less Information

My second problem with wc_customer_bought_product() is actually just a friendly critique rather than an issue.

wc_customer_bought_product() returns a boolean which really boils down the data it retrieves. Returning the quantity of products that had been bought is much more versatile. To then get a boolean is as simple as checking if the returned quantity is greater than 0. If true, then the product has been bought by the customer. This can then be quickly translated into a boolean return by making a wrapper function to do the comparison for you:

function customer_bought_product( ... ) : bool {

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

  return FALSE;

}

Querying Product Quantities by Customer

Finally! The section with solutions! The full function is at the end of this post, but I'm going to take a moment to explain its details. This way, you'll quickly be able to understand how to use it and tweak it to suite your own needs.

Why Use One Function?

Almost a third (32.6%, actually) of this function's body is simply dealing with the parameters. I'm a fan of DRY (Don't Repeat Yourself) code which aims to reduce work implementing changes and thus bugs. I didn't want to be managing the same code in multiple places for those reasons. Additionally, creating a private helper function just for parameter handling specific to these 3 functions is not good segmentation of code. As a bonus, I think the function reads a lot nicer this way in contrast to trying to remember 3 ridiculously long method 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, however, the function will use the current user's ID and email by default. I thought this was a great feature from wc_customer_bought_product(), so I decided to keep that functionality when developing this method!

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 you've learned about this method and how it differs from the WooCommerce's wc_customer_bought_product() method, here is the full code:

/**
 * 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()