通过变体 ID 获取 WooCommerce 订单项目

问题描述：

如何根据特定的产品变体列出订单项目?

How can I list order items based on a specific product variations?

给定一个变体 ID，我想生成一个包含该特定变体的所有订单项的列表.

Given a variation ID, I would like to generate a list of all order items that include that particular variation.

在我的例子中，我使用变体来表示日期，而产品本身是一个重复发生的事件，所以这样做的目的是列出参加那个特定日期的人.

In my case I'm using variations to represent dates and the product itself is a recurring event, so the purpose of this is to list the people attending that specific date.

如果这可以通过像 get_posts 使用 meta_keys 或类似的东西这样简单的东西来完成，那将是令人惊奇的，但否则我猜自定义查询会是方式.

It would be amazing if this could be accomplished by something simple like a get_posts using meta_keys or something in that vein, but otherwise I'm guessing a custom query would be the way.

我似乎无法弄清楚这些表在这种情况下是如何关联的，或者是否以可搜索的方式存储.

I just can't seem to figure out how the tables relate in this case or if this is stored in a searchable way.

非常感谢任何帮助.

谢谢！

答

有很多方法可以实现这一点.在这里，我在一个自定义函数中使用了一个 SQL 查询和一个 $variation_id (要设置的变体 ID) 作为其中的参数:

There is many ways to accomplish that. Here I use in a custom function with one SQL query and a $variation_id (the variation ID to set in) as parameter in it:

function get_all_orders_items_from_a_product_variation( $variation_id ){

    global $wpdb;

    // Getting all Order Items with that variation ID
    $item_ids_arr = $wpdb->get_col( $wpdb->prepare( "
        SELECT `order_item_id` 
        FROM {$wpdb->prefix}woocommerce_order_itemmeta 
        WHERE meta_key LIKE '_variation_id' 
        AND meta_value = %s
    ", $variation_id ) );

    return $item_ids_arr; // return the array of orders items ids

}

代码位于活动子主题(或主题)的 function.php 文件或任何插件文件中.

用法 (此处以变体 ID 41 为例):

这将显示此变体 ID 的订单商品 ID 列表以及一些数据(例如).

This will display a list of orders items IDs for this variation ID with some data (for example).

$items_ids = get_all_orders_items_from_a_product_variation( 41 );

// Iterating through each order item
foreach( $items_ids as $item_id ){

    // Getting some data (the color value here)
    $item_color = wc_get_order_item_meta( $item_id, 'pa_color', true );

    // Displaying some data related to the current order item
    echo 'Item ID: '. $item_id . ' with color "' . $item_color .'"<br>';
}

此代码已经过测试且有效.

This code is tested and works.

获取订单 ID (更新)

现在，如果您需要获取所有订单 ID，您可以通过这种方式在该函数中使用另一个查询:

Now if you need to get all the Orders IDs instead, you can use another query inside that function this way:

function get_all_orders_that_have_a_product_variation( $variation_id ){

    global $wpdb;

    // Getting all Order IDs with that variation ID
    $order_ids_arr = $wpdb->get_col( $wpdb->prepare( "
        SELECT DISTINCT items.order_id
        FROM {$wpdb->prefix}woocommerce_order_items AS items
        LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS itemmeta ON items.order_item_id = itemmeta.order_item_id
        WHERE meta_key LIKE '_variation_id'
        AND meta_value = %s
    ", $variation_id ) );

    return $order_ids_arr; // return the array of orders ids

}

代码位于活动子主题(或主题)的 function.php 文件或任何插件文件中.

用法 (例如，此处始终使用变体 ID 41):

这将显示此变体 ID 的订单 ID 列表及其状态(例如).

This will display a list of orders IDs for this variation ID with their status (for example).

$orders_ids = get_all_orders_that_have_a_product_variation( 41 );

// Iterating through each order item
foreach( $orders_ids as $order_id ){

    // Getting an instance of the order object
    $order = wc_get_order($order_id);

    // Displaying some data related to the current order
    echo 'Order #'. $order_id . ' has status "' . $order->get_status() .'"<br>';
}

此代码已经过测试且有效.

This code is tested and works.

您还可以通过这种方式将订单 ID 与其相关的商品 ID 组合到一个更复杂的数组中:

You can also combine in a more complex array, the Order ID with it's related Items IDs in a multi-dimensional array this way:

function get_all_orders_and_item_ids_that_have_a_product_variation( $variation_id ){

    global $wpdb;

    // Getting all Order IDs and item ids with that variation ID
    $results = $wpdb->get_results( $wpdb->prepare( "
        SELECT items.order_id, items.order_item_id AS item_id
        FROM {$wpdb->prefix}woocommerce_order_items AS items
        LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS itemmeta ON items.order_item_id = itemmeta.order_item_id
        WHERE meta_key LIKE '_variation_id'
        AND meta_value = %s
    ", $variation_id ), ARRAY_A );

    return $results; // return a multi-dimensional array of orders Ids / items Ids

}

通过变体 ID 获取 WooCommerce 订单项目

相关推荐