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.
