I am trying to list all of the products bought that belong to a certain product category, for instance all T-shirt orders under the "clothes" product category.
I find the Woocommerce database structure quite confusing and you have to write long queries to get simple information.
Could anyone provide an example query to get all orders by a certain category?
I know this will probably involve wp_woocommerce_order_items
and wp_terms
tables, but get lost from there.
The following SQL query will give you a list of product IDs purchased at least once that belongs to "clothes" product category slug:
SELECT DISTINCT tr.object_id
FROM wp_term_relationships tr
INNER JOIN wp_term_taxonomy tt
ON tr.term_taxonomy_id = tt.term_taxonomy_id
INNER JOIN wp_terms t
ON tt.term_id = t.term_id
INNER JOIN wp_woocommerce_order_itemmeta oim
ON tr.object_id = oim.meta_value
INNER JOIN wp_woocommerce_order_items oi
ON oim.order_item_id = oi.order_item_id
INNER JOIN wp_posts as o
ON oi.order_id = o.ID
WHERE tt.taxonomy = 'product_cat'
AND t.slug = 'clothes'
AND oim.meta_key = '_product_id'
AND o.post_type = 'shop_order'
AND o.post_status IN ('wc-completed','wc-processing')
Tested and works
Or in Wordpress using WPDB
class this can be done using:
global $wpdb;
$product_category = 'clothes'; // Term slug
$products_ids = $wpdb->get_col( "
SELECT DISTINCT tr.object_id
FROM wp_term_relationships tr
INNER JOIN wp_term_taxonomy tt
ON tr.term_taxonomy_id = tt.term_taxonomy_id
INNER JOIN wp_terms t
ON tt.term_id = t.term_id
INNER JOIN wp_woocommerce_order_itemmeta oim
ON tr.object_id = oim.meta_value
INNER JOIN wp_woocommerce_order_items oi
ON oim.order_item_id = oi.order_item_id
INNER JOIN wp_posts as o
ON oi.order_id = o.ID
WHERE tt.taxonomy = 'product_cat'
AND t.slug = '$product_category'
AND oim.meta_key = '_product_id'
AND o.post_type = 'shop_order'
AND o.post_status IN ('wc-completed','wc-processing')
");
// Pre-formatted raw display (an array of products ids)
echo '<pre>'; print_r($products_ids); echo '</pre>';
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments