WooCommerce Function of the Week: wc_get_held_stock_quantity

Welcome back to WooCommerce Function of the Week! This week we cover a little hidden gem within the WooCommerce plugin code: wc_get_held_stock_quantity(). As the name suggests, this function helps you get the amount of stock present in pending orders for a given product.

If your store relies on careful stock management — maybe because you handle products with a small inventory — wc_get_held_stock_quantity is very useful. As orders come in for a limited number of products, you can take special action. Maybe you need to cancel those orders, or maybe you want to entice the customers placing them to complete their purchases before the item goes out of stock.

So, as usual, let’s first take a look at the function syntax and then consider a practical example of how you might use or display the returned value. Enjoy!

Function syntax

You can find the wc_get_held_stock_quantity() function under woocommerce\includes\wc-stock-functions.php:

/**
 * See how much stock is being held in pending orders.
 *
 * @since 3.5.0
 * @param WC_Product $product Product to check.
 * @param integer    $exclude_order_id Order ID to exclude.
 * @return int
 */
function wc_get_held_stock_quantity( WC_Product $product, $exclude_order_id = 0 ) {
	/**
	 * Filter: woocommerce_hold_stock_for_checkout
	 * Allows enable/disable hold stock functionality on checkout.
	 *
	 * @since 4.3.0
	 * @param bool $enabled Default to true if managing stock globally.
	 */
	if ( ! apply_filters( 'woocommerce_hold_stock_for_checkout', wc_string_to_bool( get_option( 'woocommerce_manage_stock', 'yes' ) ) ) ) {
		return 0;
	}

	return ( new \Automattic\WooCommerce\Checkout\Helpers\ReserveStock() )->get_reserved_stock( $product, $exclude_order_id );
}

As you can see, there is a filter hook (apply_filters) at the very beginning of the function that allows you to disable the hold stock functionality. But we want this to be active to understand what the function does, so let’s go ahead.

As the return statement says, we need to study another function: get_reserved_stock(). Let’s do a quick file search within the WooCommerce plugin files with your favorite code editor. The only search result is in woocommerce\src\Checkout\Helpers\ReserveStock.php:

/**
 * Query for any existing holds on stock for this item.
 *
 * @param \WC_Product $product Product to get reserved stock for.
 * @param integer     $exclude_order_id Optional order to exclude from the results.
 *
 * @return integer Amount of stock already reserved.
 */
public function get_reserved_stock( $product, $exclude_order_id = 0 ) {
	global $wpdb;
	if ( ! $this->is_enabled() ) {
		return 0;
	}
	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared
	return (int) $wpdb->get_var( $this->get_query_for_reserved_stock( $product->get_stock_managed_by_id(), $exclude_order_id ) );
}

I have a feeling this will take a while…

For sure, it seems we’re querying the WordPress database now. But in order to find out what we’re looking for, we’ve got to do another file search for the get_query_for_reserved_stock function. Here we go; it’s in woocommerce\src\Checkout\Helpers\ReserveStock.php:

/**
 * Returns query statement for getting reserved stock of a product.
 *
 * @param int     $product_id Product ID.
 * @param integer $exclude_order_id Optional order to exclude from the results.
 * @return string|void Query statement.
 */
private function get_query_for_reserved_stock( $product_id, $exclude_order_id = 0 ) {
	global $wpdb;
	$query = $wpdb->prepare(
		"
		SELECT COALESCE( SUM( stock_table.`stock_quantity` ), 0 ) FROM $wpdb->wc_reserved_stock stock_table
		LEFT JOIN $wpdb->posts posts ON stock_table.`order_id` = posts.ID
		WHERE posts.post_status IN ( 'wc-checkout-draft', 'wc-pending' )
		AND stock_table.`expires` > NOW()
		AND stock_table.`product_id` = %d
		AND stock_table.`order_id` != %d
		",
		$product_id,
		$exclude_order_id
	);

	/**
	 * Filter: woocommerce_query_for_reserved_stock
	 * Allows to filter the query for getting reserved stock of a product.
	 *
	 * @since 4.5.0
	 * @param string $query            The query for getting reserved stock of a product.
	 * @param int    $product_id       Product ID.
	 * @param int    $exclude_order_id Order to exclude from the results.
	 */
	return apply_filters( 'woocommerce_query_for_reserved_stock', $query, $product_id, $exclude_order_id );
}

Fantastic! We now have our database query. Here’s what it does:

• First, the database query looks for the wc_reserved_stock table. I didn’t know that myself!
• Next, it sums all stock quantities for order IDs that are in pending / draft status.
• Then it adds the held stock that hasn’t expired yet.
• And then it counts where the order contains a given product ID.
• Finally, it counts where the order is not the excluded ID.

Excellent stuff, another cool little function to know!

But don’t forget! The “Hold stock” WooCommerce setting must be enabled, otherwise, there won’t be any held stock to count up:

Case Study

Let’s say you have product ID = 123 with stock quantity = 10, and you usually give a lot of time to your users to complete their orders. Say, “Hold stock” is, for example, 1440 [1 day in minutes]).

Wouldn’t it be cool if you could automatically trigger a reminder to customers with pending orders when their product is about to go out of stock?

To achieve this, you can simply get the product stock and subtract the held stock from it to calculate the “actual” stock currently available for all pending orders completed within a given day. If you reach a low threshold of, for example, two items left in stock, then an email could be triggered for customers with a custom message.

For the email trigger, I’ll reuse some of the code I already wrote for my “WooCommerce: Send Email to Admin Every 3 Hours” tutorial on Business Bloomer:

/**
 * @snippet       Send Pending Order Reminder If Actual Stock Low
 * @how-to        Get CustomizeWoo.com FREE
 * @author        Rodolfo Melogli
 * @testedwith    WooCommerce 6
 * @donate $9     https://businessbloomer.com/bloomer-armada/
 */

// ---- ---- ----
// A. Define a cron job interval if it doesn't exist
 
add_filter( 'cron_schedules', 'bbloomer_check_every_3_hours' );
 
function bbloomer_check_every_3_hours( $schedules ) {
    $schedules['every_three_hours'] = array(
        'interval' => 10800,
        'display'  => __( 'Every 3 hours' ),
    );
    return $schedules;
}
 
// ---- ---- ----
// B. Schedule an event unless already scheduled
 
add_action( 'wp', 'bbloomer_custom_cron_job' );
 
function bbloomer_custom_cron_job() {
   if ( ! wp_next_scheduled( 'bbloomer_send_pending_order_reminder' ) ) {
      wp_schedule_event( time(), 'every_three_hours', 'bbloomer_send_pending_order_reminder' );
   }
}
 
// ---- ---- ----
// C. Trigger email when hook runs
 
add_action( 'bbloomer_send_pending_order_reminder', 'bbloomer_generate_pending_order_reminder' );
 
// ---- ---- ----
// D. Generate email content
 
function bbloomer_generate_pending_order_reminder() {   
   $range = 180; // 3 hours in minutes
   $pending_orders = bbloomer_get_pending_orders_before_after( strtotime( '-' . absint( $end ) . ' MINUTES', current_time( 'timestamp' ) ), current_time( 'timestamp' ) );
   if ( $pending_orders ) {
      $email_subject = "Complete your order before products go out of stock!";
      foreach ( $pending_orders as $pending_order_id ) {
         $pending_order = wc_get_order( $pending_order_id );
         foreach ( $pending_order->get_items() as $item_id => $item ) {
            $product = $item->get_product();
            if ( $product && $product->managing_stock() && ( $product->get_stock_quantity() - wc_get_held_stock_quantity( $product ) ) < 3 ) { 
               $email_content = "Hello " . $pending_order->get_billing_first_name() . ", your pending order #" . $pending_order_id . " contains a product that may soon run out of stock. This is a gentle reminder so that you do not miss on the opportunity to buy now while stock lasts.";
               wp_mail( $pending_order->get_billing_email(), $email_subject, $email_content );
            }
         }
      }
   }
}
 
// ---- ---- ----
// E. Query database for pending orders between two timestamps
 
function bbloomer_get_pending_orders_before_after( $date_one, $date_two ) {
   global $wpdb;
   $pending_orders = $wpdb->get_col(
      $wpdb->prepare(
         "SELECT posts.ID
         FROM {$wpdb->prefix}posts AS posts
         WHERE posts.post_type = 'shop_order'
         AND posts.post_status = 'wc-pending'
         AND posts.post_modified >= '%s'
         AND posts.post_modified <= '%s'",
         date( 'Y/m/d H:i:s', absint( $date_one ) ),
         date( 'Y/m/d H:i:s', absint( $date_two ) )
      )
   );
   return $pending_orders;
}

This week I’ve been super busy so I’ll leave the testing to you. It will be nice to get your feedback in the comments!

Happy Woo!