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!!