从Woocommerce中的产品ID获取所有订单ID

问题描述：

如何获取具有按产品ID排序的ID的数组?

How can I get an array with Order IDs by Product ID?

我的意思是收到所有展示特定产品的订单.

I mean receive all orders where specific product is presented.

我知道如何通过MySQL执行此操作，但是有没有办法通过WP_Query函数执行此操作?

I know how to do this by MySQL, but is there a way to do this by WP_Query function?

答

更新:

2017 -SQL查询从 "SELECT" 更改为 "SELECT DISTINCT" ，从而避免数组中的重复的 订单ID (然后不需要 array_unique() 来过滤重复的…).

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 -在SQL查询中启用了产品版本类型支持

2019 - Enabled product variation type support in the SQL Query

然后您可以将其嵌入到自定义函数中，并以 $product_id 作为参数.
您必须在其中设置订单状态定位.

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

    # 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"
    );
}

此代码可在任何php文件中使用.

此代码已经过测试，可用于WooCommerce 2.5 +，2.6 +和3 +

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

用法示例:

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

从Woocommerce中的产品ID获取所有订单ID

相关推荐