Get WooCommerce orders Items by variation ID

Question

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

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.

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.

Any help very much appreciated.

Thanks!

Answer 1

I tried to do the same, after looking at db I was getting 0 in front of '_variation_id'. But got the orders items using my custom hack for the future people who are looking for the same issue. We need to compare using product_name, variaion_name such as Size etc. And variation_value LIKE 'S', 'L' etc. Consider this function:

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

}

Now call it, say our product name is Raleigh sport, variation name is size and value is s. In simple form, we want to get the order items id of the product whose name is Raleigh sport and we want to fetch only whose size is S

simple call by function:

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

thanks.

Answer 2

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

}

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

USAGE (here with the variation ID 41 for example):

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.

---

GETTING THE ORDER ID (UPDATED)

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

}

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

USAGE (here always with the variation ID 41 for example):

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.

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

}