Get all Orders IDs from a product ID

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

UPDATED: Changed the SQL query to "SELECT DISTINCT" instead of just "SELECT" to avoid duplicated Order IDs in the array (then no need of array_unique() to filter duplicates…).

With a WP Query this is not possible as I know, but using WordPress class wpdb you can easily do it including a 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 retrieve_orders_ids_from_a_product_id( $product_id ) {
    global $wpdb;

    // 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 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 LIKE '_product_id'
        AND woim.meta_value LIKE '$product_id'
        ORDER BY woi.order_item_id DESC"
    );
    // Return an array of Orders IDs for the given product ID
    return $orders_ids;
}

This code goes in any php file.

This code is tested and works for WooCommerce version 2.5+, 2.6+ and 3.0+

---

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 = retrieve_orders_ids_from_a_product_id( $product_id );

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

Answer 2

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