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

Question

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?

Answer 1

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 ) );

Answer 2

If you want your code to work in future WC updates, it is better to use functions provided by WC to get details from the DB, since WC often change the DB structure. I'd try something like:

function get_orders_id_from_product_id($product_id, $args = array() ) {
  //first get all the order ids
  $query = new WC_Order_Query( $args );
  $order_ids = $query->get_orders();
  //iterate through order
  $filtered_order_ids = array();
  foreach ($order_ids as $order_id) {
    $order = wc_get_order($order_id);
    $order_items = $order->get_items();
    //iterate through an order's items
    foreach ($order_items as $item) {
      //if one item has the product id, add it to the array and exit the loop
      if ($item->get_product_id() == $product_id) {
        array_push($filtered_order_ids, $order_id);
        break;
      }
    }
  }
  return $filtered_order_ids;
}

Usage example:

$product_id = '2094';
// NOTE: With 'limit' => 10 you only search in the last 10 orders
$args = array(
    'limit' => 10,
    'orderby' => 'date',
    'order' => 'DESC',
    'return' => 'ids',
);

$filtered_order_ids = get_orders_id_from_product_id($product_id, $args);

print_r($filtered_order_ids);