Get all Orders IDs from a product ID in Woocommerce

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?

Solution 1

Updates:

• 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…).

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

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

So here is the function that will do the job:

function get_orders_ids_by_product_id( $product_id ) {
    global $wpdb;
    
    // Define HERE the orders status to include in  <==  <==  <==  <==  <==  <==  <==
    $orders_statuses = "'wc-completed', 'wc-processing', 'wc-on-hold'";

    # Get All defined statuses Orders IDs for a defined product ID (or variation ID)
    return $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"
    );
}

This code goes in any php file.

This code is 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 in a coma separated list
echo '<p>' . implode( ', ', $orders_ids_array ) . '</p>';

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

Solution 3

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

Comments

[RiotAct]

This is not working for me. It returns empty every time.

[Nadeem0035]

@RiotAct Are you sure that current user have orders? make sure customer is login

[RiotAct]

Yes. The current user has order, is logged in. I even tested this in phpmyadmin and get an empty response (that is with the user id and product id entered and the query rewritten to be valid for phpmyadmin sql.

[Tom F.]

What should I do if I want to extend this query to also return the quantity ordered?