Get all Orders IDs from a product ID in Woocommerce (+ HPOS)

How can I get an array with Order IDs by Product ID?

I mean receive all orders where specific product is presented.

I know how to do this by MySQL, but is there a way to do this by WP_Query function?

Updates:

• 2024 update:

  • added more secured WPDB prepare() method, double forcing function argument as an integer in the query.
  • added another function with lightweight SQL query compatible with High-Performance Order Storage (HPOS).
  • SQL Query optimization using JOIN (thanks to MultiSuperFreek)

• 2019 - Enabled product variation type support in the SQL Query

• 2017 - SQL query changed to "SELECT DISTINCT" instead of "SELECT" to avoid duplicated Order IDs in the array (then no need of array_unique() to filter duplicates…).

You can embed an SQL query in a custom function with $product_id as argument.
You will have to set inside it, the order statuses that you are targeting.

1). New lightweight SQL query (compatible with High-Performance Order Storage HPOS)

WooCommerce is progressively migrating to custom tables, for better performances.

From WooCommerce 8.2, released on October 2023, High-Performance Order Storage (HPOS) is officially flagged as stable and will be enabled by default for new installations.

The fast and lightweight SQL query embedded in a function (compatible with HPOS):

// Get All defined statuses Orders IDs for a defined product ID (or variation ID)
function get_orders_ids_by_product_id( $product_id ) {
    global $wpdb;

    // HERE Define the orders status to include IN (each order status always starts with "wc-")
    $orders_statuses = array('wc-completed', 'wc-processing', 'wc-on-hold');

    // Convert order statuses array to a string for the query
    $orders_statuses = "'" . implode("', '", $orders_statuses) . "'";

    // The query
    return $wpdb->get_col( $wpdb->prepare("
        SELECT DISTINCT opl.order_id
        FROM {$wpdb->prefix}wc_order_product_lookup opl
        JOIN {$wpdb->prefix}wc_orders o ON opl.order_id = o.id
        WHERE o.type = 'shop_order'
        AND o.status IN ( {$orders_statuses} )
        AND ( opl.product_id = %d OR opl.variation_id = %d )
        ORDER BY opl.order_item_id DESC;", intval($product_id), intval($product_id) ) 
    );
}

Code goes in functions.php file of your child theme (or in a plugin).

Tested and works for WooCommerce version 8+

---

2). Old classic SQL query using WordPress and WooCommerce legacy tables (heavier, less efficient and not compatible with HPOS):

// Get All defined statuses Orders IDs for a defined product ID (or variation ID)
function get_orders_ids_by_product_id( $product_id ) {
    global $wpdb;

    // HERE Define the orders status to include IN (each order status always starts with "wc-")
    $orders_statuses = array('wc-completed', 'wc-processing', 'wc-on-hold');

    // Convert order statuses array to a string for the query
    $orders_statuses = "'" . implode("', '", $orders_statuses) . "'";

    // The query
    return $wpdb->get_col( $wpdb->prepare("
        SELECT DISTINCT woi.order_id
        FROM {$wpdb->prefix}woocommerce_order_itemmeta woim
        JOIN {$wpdb->prefix}woocommerce_order_items woi
            ON woi.order_item_id = woim.order_item_id
        JOIN {$wpdb->prefix}posts p
            ON woi.order_id = p.ID
        WHERE p.post_status IN ( {$orders_statuses} )
        AND woim.meta_key IN ( '_product_id', '_variation_id' )
        AND woim.meta_value = %d
        ORDER BY woi.order_item_id DESC;", intval($product_id) ) 
    );
}

Code goes in functions.php file of your child theme (or in a plugin).

Tested and works for WooCommerce version 2.5+, 2.6+ and 3+

---

USAGE EXAMPLES:

## This will display all orders containing this product ID in a coma separated string ##

// A defined product ID: 40
$product_id = 40;

// We get all the Orders for the given product ID in an arrray
$orders_ids = (array) get_orders_ids_by_product_id( $product_id );

// We display the orders count and the orders IDs in a coma separated string
printf( '<p>%d orders for IDs: %s</p>', count($orders_ids), implode( ', ', $orders_ids ) );