This time around we’ll take a look at some SQL. As you know, WooCommerce orders (same as WooCommerce products) are stored in the WordPress database.
Instead of using complex PHP loops and conditionals, sometimes knowing a bit of database “reading” can help. I took some inspiration (because I don’t know everything by heart) from the wc_customer_bought_product() WooCommerce function, which contains some SQL to check if a user has purchased a given product.
I’ve played a little with the same SQL SELECT call, and managed to return the list of user email addresses who have purchased a specific product ID. If you’re ever going to need this, enjoy!
Note re: HPOS
WooCommerce High-Performance Order Storage (HPOS) is a database structure designed to improve order management performance. If you’re unsure whether your store is using HPOS, go to WooCommerce > Settings > Advanced > Features and check the ‘Order data storage’ section.
Below, you’ll find two code snippets—one for stores with HPOS enabled and another for stores still using the legacy order system.
PHP Snippet 1: Get List of WooCommerce Customer Emails Who Purchased a Specific Product/Variation ID [HPOS]
Note: this only works if you have HPOS (High Performance Order Storage) enabled, which means your orders are stored in dedicated DB tables. If you don’t use WooCommerce HPOS, see Snippet 2 below.
Usage: simply call the bbloomer_get_product_customers( $product_id ) function somewhere in your code, and you will get the array of customer billing emails who bought a specific product or variation ID!
/**
* @snippet Get Customers Who Purchased Product ID [HPOS]
* @how-to businessbloomer.com/woocommerce-customization
* @author Rodolfo Melogli, Business Bloomer
* @compatible WooCommerce 9
* @community https://businessbloomer.com/club/
*/
function bbloomer_get_product_customers( $product_id ) {
global $wpdb;
$statuses = array_map( 'esc_sql', wc_get_is_paid_statuses() );
$statuses = array_map(
function ( $status ) {
return "wc-$status";
},
$statuses
);
$customer_emails = $wpdb->get_col( "
SELECT DISTINCT o.billing_email
FROM {$wpdb->prefix}wc_order_product_lookup AS lookup
INNER JOIN {$wpdb->prefix}wc_orders AS o ON lookup.order_id = o.id
WHERE o.status IN ( '" . implode( "','", $statuses ) . "' )
AND ( lookup.product_id = $product_id OR lookup.variation_id = $product_id )
" );
if ( $customer_emails ) return $customer_emails;
return false;
}
PHP Snippet 2: Get List of WooCommerce Customer Emails Who Purchased a Specific Product/Variation ID [Legacy Order Storage / No HPOS]
Usage: simply call the bbloomer_get_product_customers( $product_id ) function somewhere in your code, and you will get the array of customer billing emails who bought a specific product or variation ID!
/**
* @snippet Get Customers Who Purchased Product ID [No HPOS]
* @how-to businessbloomer.com/woocommerce-customization
* @author Rodolfo Melogli, Business Bloomer
* @compatible WooCommerce 9
* @community https://businessbloomer.com/club/
*/
function bbloomer_get_product_customers( $product_id ) {
global $wpdb;
$statuses = array_map( 'esc_sql', wc_get_is_paid_statuses() );
$customer_emails = $wpdb->get_col( "
SELECT DISTINCT pm.meta_value AS billing_email
FROM {$wpdb->prefix}wc_order_product_lookup AS lookup
INNER JOIN {$wpdb->posts} AS p ON p.ID = lookup.order_id
INNER JOIN {$wpdb->postmeta} AS pm ON p.ID = pm.post_id
WHERE p.post_status IN ( 'wc-" . implode( "','wc-", $statuses ) . "' )
AND ( lookup.product_id = $product_id OR lookup.variation_id = $product_id )
AND pm.meta_key = '_billing_email'
" );
if ( $customer_emails ) return $customer_emails;
return false;
}
Unfortunately, this code doesn’t seem to work with WooCommerce Version 9.7.0
Try now with the updated version! Let me know
This is very useful. But i wonder how i can call this dynamically via shortcode?
Hello Frode, thanks so much for your comment! Yes, this is definitely possible, but I’m afraid it’s custom work. If you’d like to get a quote, feel free to contact me here. Thanks a lot for your understanding!
Good morning, how do I specify which specific product I want to sort?
Hi Bartosz, you can use the function anywhere you need, simply specify the product ID:
Hello! How would I go about to find users IDs instead of emails?
Hi Rafa, thanks so much for your comment! Yes, this is definitely possible, but I’m afraid it’s custom work. If you’d like to get a quote, feel free to contact me here. Thanks a lot for your understanding!
Great – thank you. Still working with v. 4.9, Jan 2021. Super helpful.
Awesome!
Still works in WooCommerce 4.01
Ha meant to say 4.0! not 4.01 🙂
Nice!
Using the Code Snippets plugin. Once the code snippet is activated, where should I look to see the results?
Hi Mark! It depends where you need to display them. For example, this is how to add a new WP admin subpage: https://businessbloomer.com/woocommerce-add-new-subpage-wordpress-admin-dashboard/
This is a very handy one, thanks Rodolfo
Great!