Get All Orders Ids from a Product Id in Woocommerce

Get all Orders IDs from a product ID in Woocommerce

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

Get the orders IDs related to a product bought by a customer in Woocommerce

The following custom function made with a very light unique SQL query, will get all the Orders IDs from an array of products IDs (or a unique product ID) for a given customer.

Based on code from: Check if a customer has purchased a specific products in WooCommerce

function get_order_ids_from_bought_items( $product_ids = 0, $customer_id = 0 ) {
    global $wpdb;

    $customer_id = $customer_id == 0 || $customer_id == '' ? get_current_user_id() : $customer_id;
    $statuses    = array_map( 'esc_sql', wc_get_is_paid_statuses() );

    if ( is_array( $product_ids ) )
        $product_ids = implode(',', $product_ids);

    if ( $product_ids !=  ( 0 || '' ) )
        $meta_query_line = "AND woim.meta_value IN ($product_ids)";
    else
        $meta_query_line = "AND woim.meta_value != 0";

    // Get Orders IDs
    $results = $wpdb->get_col( "
        SELECT DISTINCT p.ID FROM {$wpdb->prefix}posts AS p
        INNER JOIN {$wpdb->prefix}postmeta AS pm ON p.ID = pm.post_id
        INNER JOIN {$wpdb->prefix}woocommerce_order_items AS woi ON p.ID = woi.order_id
        INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS woim ON woi.order_item_id = woim.order_item_id
        WHERE p.post_status IN ( 'wc-" . implode( "','wc-", $statuses ) . "' )
        AND pm.meta_key = '_customer_user'
        AND pm.meta_value = $customer_id
        AND woim.meta_key IN ( '_product_id', '_variation_id' )
        $meta_query_line
    " );

    // Return an array of Order IDs or an empty array
    return sizeof($results) > 0 ? $results : array();
}

Code goes in function.php file of your active child theme (or active theme). Tested and works.

---

USAGE Examples:

1) For the current logged in customer (and 2 product Ids in an array):

$product_ids = array(37,53);
$order_ids = get_order_ids_from_bought_items( $product_ids );

2) For a defined User ID and one product ID:

$product_id = 53;
$user_id    = 72;
$order_ids  = get_order_ids_from_bought_items( $product_id, $user_id );

How to get list of order id in woocommerce

Please try this code and let me know if it works for you. I haven't tested it yet...

global $woocommerce, $product;
$args = array(
'post_type' => 'shop_order',
'posts_per_page' => '-1'
);
$my_query = new WP_Query($args);

$orders = $my_query->posts;
echo "</pre>";
print_r($orders);//this will get you all the orders from backend
echo "<pre>";
foreach ($orders as $order){

$order = new WC_Order( $order['id'] );//here get the orderid from $orders array
$items = $order->get_items(); // this will get all product details under orderid
foreach ( $items as $item ) {
$product_name = $item['name'];
$product_id = $item['product_id'];
$product_variation_id = $item['variation_id'];
}

}

Woocommerce: Get all orders for a product

Edited
This function doesn't exist, but it can be built. So the function below will return an array of all orders IDs for a given product ID, making the right SQL query:

/**
 * Get All orders IDs for a given product ID.
 *
 * @param  integer  $product_id (required)
 * @param  array    $order_status (optional) Default is 'wc-completed'
 *
 * @return array
 */
function get_orders_ids_by_product_id( $product_id, $order_status = array( 'wc-completed' ) ){
    global $wpdb;

    $results = $wpdb->get_col("
        SELECT order_items.order_id
        FROM {$wpdb->prefix}woocommerce_order_items as order_items
        LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta as order_item_meta ON order_items.order_item_id = order_item_meta.order_item_id
        LEFT JOIN {$wpdb->posts} AS posts ON order_items.order_id = posts.ID
        WHERE posts.post_type = 'shop_order'
        AND posts.post_status IN ( '" . implode( "','", $order_status ) . "' )
        AND order_items.order_item_type = 'line_item'
        AND order_item_meta.meta_key = '_product_id'
        AND order_item_meta.meta_value = '$product_id'
    ");

    return $results;
}

USAGE 1 (for a given product ID 37 and default Completed orders status):

$orders_ids = get_orders_ids_by_product_id( 37 );

// The output (for testing)
print_r( $orders_ids );

USAGE 2 (for a given product ID 37 and some defined orders statuses):

// Set the orders statuses
$statuses = array( 'wc-completed', 'wc-processing', 'wc-on-hold' );

$orders_ids = get_orders_ids_by_product_id( 37, $statuses );

// The output (for testing)
print_r( $orders_ids );

Code goes in function.php file of your active child theme (or theme) or also in any plugin file.

This code is tested and works.

Get last order ID from a purchased product ID in Woocommerce

function get_order_ids_from_bought_items() {

    $prod_arr = array('37', '53');
    $purchased = false;

    $customer_orders = get_posts(array(
        'numberposts' => -1,
        'post_type' => 'shop_order',
        'meta_key' => '_customer_user',
        'meta_value' => get_current_user_id(),
            ));
    foreach ($customer_orders as $order_post) {

        $order = wc_get_order($order_post->ID);

        foreach ($order->get_items() as $item) {

            $product_id = $item->get_product_id();

            if (in_array($product_id, $prod_arr))
                $purchased = true;
        }
    }

    return $purchased; // $order_post->ID  for Order ID
}

Get product IDs while using the woocommerce_payment_complete hook

If you use Woocommerce 3.0+ version, then it should be this.

I found the answer with this link: https://wordpress.stackexchange.com/questions/97176/get-product-id-from-order-id-in-woocommerce

In an order can be multiple products, so you have to loop through them. In your code it would look like this:

add_action( 'woocommerce_payment_complete', 'so_payment_complete' );
function so_payment_complete($order_id)
{
    $order = wc_get_order($order_id);
    $billingEmail = $order->billing_email;
    $billingName = $order->billing_first_name;

    $items = $order->get_items();

    foreach ( $items as $item ) {
        $product_name = $item->get_name();
        $product_id = $item->get_product_id();
        $product_variation_id = $item->get_variation_id();
        if ($product_id == 980) {
            // ....
        }
    }
}

How to get product category ids from order items in WooCommerce

Use the following instead to get the product category term names for each order item:

function get_order_detail( $order_id ) {
    $order = wc_get_order( $order_id );

    foreach ( $order->get_items() as $item_id => $item ) { 
        // Get the product category(ies) term(s) name(s) - (array)
        $term_names = wp_get_post_terms( $item->get_product_id(), 'product_cat', array('fields' => 'names') );
        // Set them as a coma separated string
        $categories_string = implode(',', $term_names);

    }
}

or you can get the product category Ids using the following:

function get_order_detail( $order_id ) {
    $order = wc_get_order( $order_id );

    foreach ( $order->get_items() as $item_id => $item ) { 
        // Get the product category(ies) term(s) Id(s) - (array)
        $term_ids = wp_get_post_terms( $item->get_product_id(), 'product_cat', array('fields' => 'ids') );
    }
}

This will work for all product types even variations of a variable product.

How to get product id using order items id in woocommerce

You can get product_id by using $order->get_items() function in WooCommerce

$order = new WC_Order( $order_id );
    $items = $order->get_items();
    foreach ( $items as $item ) {
        $item_id = $item['order_item_id']; 
        $product_name = $item['name'];
        $product_id = $item['product_id'];
    }

---

Related Topics