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

Here is an improved version compatible with HPOS and in theory does not rely on the wc_order_lookup table (as it might not always contain the newest information).

function get_order_ids_by_productId($product_id, $statuses = ['wc-completed']){
    global $wpdb;

    $orders_statuses = "'" . implode("', '", $statuses) . "'";

    // High-Performance Order Storage (HPOS) table names
    $orders_table         = $wpdb->prefix . 'wc_orders';
    $order_items_table    = $wpdb->prefix . 'woocommerce_order_items';
    $order_itemmeta_table = $wpdb->prefix . 'woocommerce_order_itemmeta';

    // SQL query to get order IDs by product ID
    $order_ids = $wpdb->get_col($wpdb->prepare("
        SELECT DISTINCT o.ID
        FROM {$orders_table} AS o
        INNER JOIN {$order_items_table} AS oi ON o.ID = oi.order_id
        INNER JOIN {$order_itemmeta_table} AS oim ON oi.order_item_id = oim.order_item_id
        WHERE o.type = 'shop_order'
        AND o.status  IN ( {$orders_statuses} )
        AND oim.meta_key = '_product_id'
        AND oim.meta_value = %d
    ", $product_id));

    return $order_ids;
}

    

Answer 2

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 3

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

Answer 4

Modified function to get specific user product ids

function retrieve_orders_ids_from_a_product_id( $product_id,$user_id )
{
    global $wpdb;

    $table_posts = $wpdb->prefix . "posts";
    $table_postmeta = $wpdb->prefix . "postmeta";
    $table_items = $wpdb->prefix . "woocommerce_order_items";
    $table_itemmeta = $wpdb->prefix . "woocommerce_order_itemmeta";

    // Define HERE the orders status to include in  <==  <==  <==  <==  <==  <==  <==
    $orders_statuses = "'wc-completed', 'wc-processing', 'wc-on-hold'";

    # Requesting All defined statuses Orders IDs for a defined product ID
    $orders_ids = $wpdb->get_col( "
        SELECT DISTINCT $table_items.order_id
        FROM $table_itemmeta, $table_items, $table_posts , $table_postmeta
        WHERE  $table_items.order_item_id = $table_itemmeta.order_item_id
        AND $table_items.order_id = $table_posts.ID
        AND $table_posts.post_status IN ( $orders_statuses )
        AND $table_postmeta.meta_key LIKE '_customer_user'
        AND $table_postmeta.meta_value LIKE  '$user_id '
        AND $table_itemmeta.meta_key LIKE '_product_id'
        AND $table_itemmeta.meta_value LIKE '$product_id'
        ORDER BY $table_items.order_item_id DESC"
    );
    // return an array of Orders IDs for the given product ID
    return $orders_ids;
}

Usage Example

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

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

// Current User
$current_user = wp_get_current_user();

// We get all the Orders for the given product ID of current user in an arrray
$orders_ids_array = retrieve_orders_ids_from_a_product_id( $product_id, $current_user->ID );

// We display the orders in a coma separated list
echo '<p>' . implode( ', ', $orders_ids_array ) . '</p>';

Answer 5

I'd like to note that the above answer will return a duplicate of the order_id if the order has multiple items in it.

E.g.

If there was a product called "apples" with product_id=>1036

Customer puts "apples" 3 times in their cart and purchases it, creating order_id=>555

If I query product_id->1036, I will get array(555,555,555).

There's probably an SQL way of doing this which may be faster, (would appreciate anyone that could add to this), otherwise I used: array_unqiue() to merge the duplicates.

function retrieve_orders_ids_from_a_product_id( $product_id )
{
    global $wpdb;

    $table_posts = $wpdb->prefix . "posts";
    $table_items = $wpdb->prefix . "woocommerce_order_items";
    $table_itemmeta = $wpdb->prefix . "woocommerce_order_itemmeta";

    // Define HERE the orders status to include in  <==  <==  <==  <==  <==  <==  <==
    $orders_statuses = "'wc-completed', 'wc-processing', 'wc-on-hold'";

    # Requesting All defined statuses Orders IDs for a defined product ID
    $orders_ids = $wpdb->get_col( "
        SELECT $table_items.order_id
        FROM $table_itemmeta, $table_items, $table_posts
        WHERE  $table_items.order_item_id = $table_itemmeta.order_item_id
        AND $table_items.order_id = $table_posts.ID
        AND $table_posts.post_status IN ( $orders_statuses )
        AND $table_itemmeta.meta_key LIKE '_product_id'
        AND $table_itemmeta.meta_value LIKE '$product_id'
        ORDER BY $table_items.order_item_id DESC"
    );
    // return an array of Orders IDs for the given product ID
    $orders_ids = array_unique($orders_ids);
    return $orders_ids;
}