How to Programmatically Retrieve Order IDs, Total Number of Orders for a Product in WooCommerce?

WooCommerce order IDs are retrieved and used on multiple pages of your online stores. However, their purpose varies depending on the location. In this post, we will retrieve the order IDs and the total number of orders for a product and display them on the individual product page. But why? 

Nowadays, customers’ purchase decisions are a lot more dependent on reviews. Similarly, by showing the total number of orders for each product, potential buyers will gain more trust in the quality and popularity of the item. Furthermore, you have the option to provide customers with a list of order IDs from individuals who have purchased the product which is also covered here.

This post will help you to programmatically retrieve order IDs, the total number of orders for a product, Order Dates below each order IDs on the product page in WooCommerce.

Where to Add Custom Code in WooCommerce?

It is advisable to add the code snippets to the functions.php file of your child theme. Access the file directly from Appearance->Theme File Editor->Locating the child theme’s functions.php from the right sidebar. You can also access it from your theme’s directory file. Insert the following code snippet in functions.php. The alternative & easy option is to install & activate the Code Snippets plugin. You can then add the code as a new snippet via the plugin.

Solution: Programmatically Retrieve the Total Number of Orders for a Product in WooCommerce

Imagine an online store selling electronic products such as televisions, smartphones, etc. If the store owner wants to provide assurance about the product quality and popularity of each product, then the below code snippets will help to display the text “total orders placed so for this product: “

// Function to retrieve the total number of orders by product ID
function ts_get_total_orders_by_product_id($product_id) {
    global $wpdb;

    // Define the order statuses to include
    $orders_statuses = "'wc-completed', 'wc-processing', 'wc-on-hold'";

    // Get the total number of orders that contain the specified product ID
    $total_orders = $wpdb->get_var("
        SELECT COUNT(DISTINCT woi.order_id)
        FROM {$wpdb->prefix}woocommerce_order_itemmeta as woim,
             {$wpdb->prefix}woocommerce_order_items as woi,
             {$wpdb->prefix}posts as p
        WHERE woi.order_item_id = woim.order_item_id
            AND woi.order_id = p.ID
            AND p.post_status IN ( $orders_statuses )
            AND woim.meta_key IN ( '_product_id', '_variation_id' )
            AND woim.meta_value LIKE '$product_id'
    ");

    return $total_orders;
}

// Display the total number of orders for a specific product on the individual product page
function ts_display_total_orders_on_individual_product() {
    if (is_product()) {
        global $product;
        $product_id = $product->get_id();
        $total_orders = ts_get_total_orders_by_product_id($product_id);

        if ($total_orders > 0) {
            echo 'Total orders placed so far for this product: ' . $total_orders;
        } else {
            echo 'No orders found for this product.';
        }
    }
}

add_action('woocommerce_single_product_summary', 'ts_display_total_orders_on_individual_product', 90);

Output

Use case: Retrieve the Total number of Orders of a Product

The output shows that the text message is displayed on the product page to notify customers that this television has already been bought by 8 customers.

Solution: Programmatically Retrieve Order IDs by Product ID in WooCommerce

The code will implement a feature where, on each individual product page, customers can see a section labeled ‘Order IDs that have purchased this product:’ and shows a list of order IDs below the Add To Cart button. We are making just a little bit of changes to the above code. Instead of returning total orders, we are returning the Order IDs in the below code.

// Function to retrieve order IDs by product ID
function ts_get_orders_ids_by_product_id($product_id) {
    global $wpdb;
    
    // Define the order statuses to include
    $orders_statuses = "'wc-completed', 'wc-processing', 'wc-on-hold'";

    // Get all orders that contain the specified product ID
    $order_ids = $wpdb->get_col( "
        SELECT DISTINCT woi.order_id
        FROM {$wpdb->prefix}woocommerce_order_itemmeta as woim, 
             {$wpdb->prefix}woocommerce_order_items as woi, 
             {$wpdb->prefix}posts as p
        WHERE  woi.order_item_id = woim.order_item_id
        AND woi.order_id = p.ID
        AND p.post_status IN ( $orders_statuses )
        AND woim.meta_key IN ( '_product_id', '_variation_id' )
        AND woim.meta_value LIKE '$product_id'
        ORDER BY woi.order_item_id DESC"
    );

    return $order_ids;
}

// Display order IDs for a specific product ID on the individual product page
function ts_display_order_ids_on_individual_product() {
    if (is_product()) {
        global $product;
        $product_id = $product->get_id();
        $order_ids = ts_get_orders_ids_by_product_id($product_id);

        if (!empty($order_ids)) {
            echo 'Order IDs that have purchased this product:<br>';
            foreach ($order_ids as $order_id) {
                echo $order_id . '<br>';
            }
        } else {
            echo 'No orders found for this product.';
        }
    }
}

add_action('woocommerce_single_product_summary', 'ts_display_order_ids_on_individual_product', 90);

Output

Use case: Retrieve Order IDs on the Product Page

The image below displays a list of order IDs from previous purchases of this television product. Similarly, the list of order IDs will be visible on all individual product pages associated with that specific product.

Use case: No Order IDs that have purchased this product

If any product that has no previous history of being bought by anyone then it displays the message “No orders found for this product“

Code Explanation

Step 1: Defining the Function

• The code starts by defining a custom function named ts_get_orders_ids_by_product_id. This function is designed to retrieve order IDs associated with a specific product.

Step 2: Setting up Global Database Access

• The global $wpdb; statement is used to access the global WordPress database object, $wpdb, which allows for direct database queries.

Step 3: Defining Order Statuses

• An array of order statuses, $orders_statuses, is created. These statuses determine which orders will be considered for retrieving order IDs. The chosen statuses include ‘wc-completed,’ ‘wc-processing,’ and ‘wc-on-hold.’

Step 4: Executing the Database Query

• The wpdb->get_col method is employed to execute a complex database query. This query retrieves order IDs based on specific criteria:
  • It selects distinct order IDs (woi.order_id) from three database tables: woocommerce_order_itemmeta, woocommerce_order_items, and posts.
  • It ensures that woi.order_item_id matches woim.order_item_id and that woi.order_id corresponds to p.ID.
  • It filters orders based on their status using the p.post_status field and the defined $orders_statuses.
  • It identifies order items related to a specific product or variation using the woim.meta_key and woim.meta_value fields. The product ID is specified by the $product_id variable.
  • The results are sorted in descending order based on woi.order_item_id.

Step 5: Returning Order IDs

• The retrieved order IDs are stored in the $order_ids variable.
• Finally, the function returns the $order_ids array, containing the order IDs associated with the specified product.

Step 6: Defining Another Function

• This section of the code defines another custom function named ts_display_order_ids_on_individual_product. This function is responsible for displaying the order IDs on individual product pages.

Step 7: Checking Page Context

• Inside the ts_display_order_ids_on_individual_product function, it begins by checking whether the current page is an individual product page using the is_product() function.

Step 8: Retrieving Product ID

• If the page is indeed an individual product page, it retrieves the product’s ID using $product->get_id(), assuming that $product represents the currently viewed product.

Step 9: Fetching Order IDs

• The function then calls the get_orders_ids_by_product_id($product_id) function defined earlier to obtain the order IDs associated with the current product. These order IDs are stored in the $order_ids variable.

Step 10: Displaying Order IDs

• If there are order IDs associated with the product (i.e., $order_ids is not empty), the function displays them as a list with the label “Order IDs that have purchased this product.”
• The foreach loop is used to iterate through the order IDs and display each one on a new line.

Step 11: Handling No Orders

• If no order IDs are found for the product, it displays a message stating: “No orders found for this product.”

Step 12: Hook to Display Order IDs on Individual Product Page

• The custom function ts_display_order_ids_on_individual_product is attached to the woocommerce_single_product_summary hook with a priority of 90, executing it on individual WooCommerce product pages.

Display Order Dates Below Each Order ID on WooCommerce Product Page

We have explored how to view past purchase history details of a product on its specific product page, including the number of orders placed and displaying the order IDs for each product. Now, let’s take it a step further and include the dates on which the orders were placed below each order ID. This customization will help customers to understand how recently the product was bought, indicating its current trend. 

Solution:  Display Order Dates Below Each Order ID on WooCommerce Product Page

The code dynamically displays both order IDs and order Dates on each product page.

// Function to retrieve order IDs and order dates by product ID
function ts_get_orders_ids_and_dates_by_product_id($product_id) {
    global $wpdb;

    // Define the order statuses to include
    $orders_statuses = "'wc-completed', 'wc-processing', 'wc-on-hold'";

    // Get all orders that contain the specified product ID
    $results = $wpdb->get_results( "
        SELECT DISTINCT woi.order_id, DATE(p.post_date) as order_date
        FROM {$wpdb->prefix}woocommerce_order_itemmeta as woim,
        {$wpdb->prefix}woocommerce_order_items as woi,
        {$wpdb->prefix}posts as p
        WHERE woi.order_item_id = woim.order_item_id
        AND woi.order_id = p.ID
        AND p.post_status IN ( $orders_statuses )
        AND woim.meta_key IN ( '_product_id', '_variation_id' )
        AND woim.meta_value LIKE '$product_id'
        ORDER BY woi.order_item_id DESC"
    );

    return $results;
}

// Display order IDs and order dates for a specific product ID on the individual product page
function ts_display_order_ids_and_dates_on_individual_product() {
    if (is_product()) {
        global $product;
        $product_id = $product->get_id();
        $orders = ts_get_orders_ids_and_dates_by_product_id($product_id);

        if (!empty($orders)) {
            echo '<h2>Orders that have purchased this product:</h2>';
            echo '<ul>';
            foreach ($orders as $order) {
                echo '<li>';
                echo 'Order ID: ' . $order->order_id . '<br>';
                echo 'Order Date: ' . $order->order_date;
                echo '</li>';
            }
            echo '</ul>';
        } else {
            echo 'No orders found for this product.';
        }
    }
}

add_action('woocommerce_single_product_summary', 'ts_display_order_ids_and_dates_on_individual_product', 90);

Output

When a customer visits any product page, for example, a t-shirt, the page will display the previous order IDs placed for this product, along with the order dates below the order IDs.

Conclusion

This post has discussed how to display order IDs for all products. However, you can also restrict the display of order IDs to specific products in your store by specifying the product ID in the code.

In addition to this, if you are looking to customize the WooCommerce order numbers, you can use the Custom Order Numbers for WooCommerce plugin which numbers the orders in a way that is convenient for your business.

Let us know how the code was useful or any other queries in the comments section.