I have a WooCommerce shop. I have a custom user meta key for user expiration that I need to update based on the result of all users who have purchased a specific product.
Unfortunately, I have not found a way within WooCommerce to get a list of users who have purchased a specific product. So I have resorted to a custom MySQL query as follows:
SELECT
u1.ID,
u1.post_date,
u1.post_type,
u1.post_status,
m2.meta_value AS customer_id, # WooCommerce customer ID from post meta
o3.order_item_name AS item_name, # The WooCommerce Product Item name
e4.meta_value AS expires # A custom user meta field called "expires" formatted as MM/DD/YYYY
FROM wp_posts u1
JOIN wp_postmeta m2 ON (m2.post_id = u1.ID AND m2.meta_key = '_customer_user')
JOIN wp_woocommerce_order_items o3 ON (o3.order_id = u1.ID )
JOIN wp_usermeta e4 ON (e4.user_id = m2.meta_value AND e4.meta_key = 'expires')
WHERE u1.post_type = "shop_order" # WooCommerce orders are custom post type "shop_order"
AND u1.post_status = "wc-completed" # Only dealing with post_status "wc-completed" which is a completed WC order
AND o3.order_item_name = "My Fancy Product" # String value for item name in the WC order item table
AND STR_TO_DATE( e4.meta_value, '%m/%d/%Y' ) > STR_TO_DATE( '01/01/2020', '%m/%d/%Y' ) # Format custom user meta (string) as a date
ORDER BY STR_TO_DATE( e4.meta_value, '%m/%d/%Y' ) # Order by custom user meta (string) as a date
To some this may be messy, but it seems to be the only way I've been able to find to do it.
The query is a join getting all WC completed orders where the item name is "My Fancy Product" (just a placeholder for the question) AND the user meta key "expires" is greater than 1/1/2020 (that's Jan 1, 2020, and yes, I know the format is meh, but it is what it is).
I've commented the SQL query with some identifying information that I hope is helpful to whomever can answer this. Here's the question:
The query is fine to return a result set. I need to run an update query to update the "expires" meta key with a new value that is their existing value but 2019 as the year. I could probably figure out parsing the current value and constructing the date myself, but what I do not know how to do is run an update on this result set as the join is what is messing me up.
If you have any ideas on how to do it with MySQL, OR if you have a better idea, that would be great!!
I have a WooCommerce shop. I have a custom user meta key for user expiration that I need to update based on the result of all users who have purchased a specific product.
Unfortunately, I have not found a way within WooCommerce to get a list of users who have purchased a specific product. So I have resorted to a custom MySQL query as follows:
SELECT
u1.ID,
u1.post_date,
u1.post_type,
u1.post_status,
m2.meta_value AS customer_id, # WooCommerce customer ID from post meta
o3.order_item_name AS item_name, # The WooCommerce Product Item name
e4.meta_value AS expires # A custom user meta field called "expires" formatted as MM/DD/YYYY
FROM wp_posts u1
JOIN wp_postmeta m2 ON (m2.post_id = u1.ID AND m2.meta_key = '_customer_user')
JOIN wp_woocommerce_order_items o3 ON (o3.order_id = u1.ID )
JOIN wp_usermeta e4 ON (e4.user_id = m2.meta_value AND e4.meta_key = 'expires')
WHERE u1.post_type = "shop_order" # WooCommerce orders are custom post type "shop_order"
AND u1.post_status = "wc-completed" # Only dealing with post_status "wc-completed" which is a completed WC order
AND o3.order_item_name = "My Fancy Product" # String value for item name in the WC order item table
AND STR_TO_DATE( e4.meta_value, '%m/%d/%Y' ) > STR_TO_DATE( '01/01/2020', '%m/%d/%Y' ) # Format custom user meta (string) as a date
ORDER BY STR_TO_DATE( e4.meta_value, '%m/%d/%Y' ) # Order by custom user meta (string) as a date
To some this may be messy, but it seems to be the only way I've been able to find to do it.
The query is a join getting all WC completed orders where the item name is "My Fancy Product" (just a placeholder for the question) AND the user meta key "expires" is greater than 1/1/2020 (that's Jan 1, 2020, and yes, I know the format is meh, but it is what it is).
I've commented the SQL query with some identifying information that I hope is helpful to whomever can answer this. Here's the question:
The query is fine to return a result set. I need to run an update query to update the "expires" meta key with a new value that is their existing value but 2019 as the year. I could probably figure out parsing the current value and constructing the date myself, but what I do not know how to do is run an update on this result set as the join is what is messing me up.
If you have any ideas on how to do it with MySQL, OR if you have a better idea, that would be great!!
Share Improve this question asked Nov 13, 2018 at 18:46 butlerblogbutlerblog 5,1413 gold badges28 silver badges44 bronze badges1 Answer
Reset to default 0I did manage to figure out a solution. It's not pure MySQL, but rather uses some WP/PHP wizardry. So this is how I solved the problem - but I'm still open to better solutions (particularly a pure MySQL method).
I used the $wpdb->get_results() object method to retrieve the results of the SQL query. That returns the results as an array (specifying ARRAY_A for the returned results).
Then looping through the results, convert the date (which is a string, as all user meta values are) using PHP's strtotime(), subtracting 1 year and returning that result to the (yes, meh) m/d/Y format required, finally updating the user meta with the new date using update_user_meta() (the user ID being in the SQL result as 'customer_id').
Personally, but not important to the answer, I also echoed the results to the screen so I could keep a record of what records were updated. But that could have been stored as a user meta during the processing as well.
I've commented all code and the MySQL query below for clarity:
// Use the $wpdb database object class.
global $wpdb;
// MySQL query
$sql = "SELECT
u1.ID,
u1.post_date,
u1.post_type,
u1.post_status,
m2.meta_value AS customer_id, # WooCommerce customer ID from post meta
o3.order_item_name AS item_name, # The WooCommerce Product Item name
e4.meta_value AS expires # A custom user meta field called expires formatted as MM/DD/YYYY
FROM wp_posts u1
JOIN wp_postmeta m2 ON (m2.post_id = u1.ID AND m2.meta_key = '_customer_user')
JOIN wp_woocommerce_order_items o3 ON (o3.order_id = u1.ID )
JOIN wp_usermeta e4 ON (e4.user_id = m2.meta_value AND e4.meta_key = 'expires')
WHERE u1.post_type = 'shop_order' # WooCommerce orders are custom post type shop_order
AND u1.post_status = 'wc-completed' # Only dealing with post_status wc-completed which is a completed WC order
AND o3.order_item_name = 'My Fancy Product' # String value for item name in the WC order item table
AND STR_TO_DATE( e4.meta_value, '%m/%d/%Y' ) > STR_TO_DATE( '01/01/2020', '%m/%d/%Y' ) # Format custom user meta (string) as a date
ORDER BY STR_TO_DATE( e4.meta_value, '%m/%d/%Y' ); # Order by custom user meta (string) as a date";
// Get results of $sql query as an array.
$results = $wpdb->get_results( $sql, ARRAY_A );
// Go through results and update user.
foreach( $results as $result ) {
// Take current "expires" and get a value -1 year.
$new_expires = date( 'm/d/Y', strtotime( $result['expires'] . ' -1 year' ) );
// Update the "expires" meta with the new value.
update_user_meta( $result['customer_id'], 'expires', $new_expires );
}