Get WooCommerce Orders Items by Variant ID

Question

How can I list order items based on specific product options?

Given the variation identifier , I would like to create a list of all the order elements that include this particular change.

In my case, I use options to represent dates, and the product itself is a recurring event, so the purpose of this is to list people visiting this particular date.

It would be surprising if this could be achieved with the help of something simple, such as get_postsusing meta_keysor something like that, but otherwise I assume that the user request will be like that.

I just can’t understand how these tables are related in this case or if they are searchable.

Any help is greatly appreciated.

Thank!

Answer 1

There are many ways to achieve this. Here I use a user-defined function with one SQL query and (change identifier for installation) as a parameter in it: $variation_id

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

}

The code goes in the function.php file of your active child theme (or theme), as well as in any plug-in file.

USAGE (here, for example, with option ID 41):

This will display a list of order item identifiers for this variant identifier 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 has been verified and works.

---

()

, , :

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 $orders_ids; // return the array of orders ids

}

function.php ( ), .

( ID 41):

().

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

.

:

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

}

Answer 2

, db, 0 '_variation_id'. , , . product_name, variaion_name .. variation_value LIKE 'S', 'L' .. :

function get_all_orders_that_have_a_product_variation( $product_name, $variation_name, $variation_value ){

global $wpdb;
// Getting all Order IDs with that variation ID

$order_ids_arr = $wpdb->get_col($wpdb->prepare ( "
        SELECT * FROM wpcc_woocommerce_order_items items 
        LEFT JOIN wpcc_woocommerce_order_itemmeta itemmeta ON items.order_item_id = itemmeta.order_item_id 
        WHERE items.order_item_name LIKE %s 
        AND itemmeta.meta_key LIKE %s 
        AND itemmeta.meta_value = %s", $product_name, $variation_name, $variation_value ));


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

}

, , Raleigh sport, size s. , Raleigh sport, , size s

:

print_r(get_all_orders_that_have_a_product_variation( "Raleigh Sport", "Size", "S" ));

.