Get All Orders For A Product In WooCommerce

WooCommerce

Over the last year I have spent a good amount of time working with multiple WooCommerce powered websites. As the application becomes more complex, I am finding myself digging deeper into WooCommerce to find out how (or why) something works. I received a feature request that would require finding all orders for a product.

The Problem

Retrieve all orders for given a product.

The product ID is stored as meta for an Order’s items; wp_posts > wp_woocommerce_order_items > wp_woocommerce_order_itemmeta. Unfortunately, this is more complicated than just using WP_Query to grab orders.

My Solution

The woocommerce_order_items has a reference to the Order ID with the order_id field, but thewoocommerce_order_itemmeta has a Product ID in the meta_value as a string. Joining these two tables will allow us to grab the Order ID, but since the Product ID is stored in the meta_value field, a dynamic column will have to be created to evaluate against the Product ID.

SELECT
  `items`.`order_id`,
  MAX(CASE WHEN `itemmeta`.`meta_key` = '_product_id' THEN `itemmeta`.`meta_value` END) AS `product_id`
FROM
  `wp_woocommerce_order_items` AS `items`
  INNER JOIN
  `wp_woocommerce_order_itemmeta` AS `itemmeta`
ON
  `items`.`order_item_id` = `itemmeta`.`order_item_id`
WHERE
  `items`.`order_item_type` IN('line_item')
AND
  `itemmeta`.`meta_key` IN('_product_id')
GROUP BY
  `items`.`order_item_id`
HAVING
  `product_id` = 8 # Product ID

Unfortunately, a WHERE clause will not evaluate against dynamic columns like product_id. Instead, a HAVING clause will was used to reduce the result set to a specific product_id. HAVING statements are evaluated after the select is completed, which could slow the query down for a large result set.

I put together a little function to return an array of WC_Order objects for a given Product ID.

/**
* Get all orders given a Product ID.
*
* @global $wpdb
*
* @param integer $product_id The product ID.
*
* @return array An array of WC_Order objects.
*/
function example_get_orders_by_product( $product_id ) {

    global $wpdb;

    $raw = "
        SELECT
          `items`.`order_id`,
          MAX(CASE WHEN `itemmeta`.`meta_key` = '_product_id' THEN `itemmeta`.`meta_value` END) AS `product_id`
        FROM
          `{$wpdb->prefix}woocommerce_order_items` AS `items`
        INNER JOIN
          `{$wpdb->prefix}woocommerce_order_itemmeta` AS `itemmeta`
        ON
          `items`.`order_item_id` = `itemmeta`.`order_item_id`
        WHERE
          `items`.`order_item_type` IN('line_item')
        AND
          `itemmeta`.`meta_key` IN('_product_id')
        GROUP BY
          `items`.`order_item_id`
        HAVING
          `product_id` = %d";

    $sql = $wpdb->prepare( $raw, $product_id );

    return array_map(function ( $data ) {
        return wc_get_order( $data->order_id );
    }, $wpdb->get_results( $sql ) );

}

Found a more efficient way to accomplish this? Let me know.

Author: rfmeier

Product developer at WPEngine and runner from Schaumburg, Illinois. I work with php and Python; Spending most of my time digging into source code to see how it all works.