Last active
October 24, 2017 00:27
-
-
Save jchristopher/d1f4d506b3f272488d91107899f7ab9b to your computer and use it in GitHub Desktop.
Get EDD Customer email and license key for Customers with ! 'active' Recurring subscriptions
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# THIS IS NOT TESTED ENOUGH !!!!!!!!!!!!!!!!!! EXPECT INACCURACIES !!!!!!!!!!!!!!!!!! | |
# THIS IS CRAZY CRAZY SLOW | |
# ADJUST THE LIMIT ON LINE 20 ACCORDINGLY (READ: IF YOU DARE) | |
SELECT * | |
FROM ( | |
SELECT | |
SUBSTRING_INDEX(SUBSTRING_INDEX(edd_customers.name, ' ', 1), ' ', -1) as firstname, | |
edd_customers.email AS email, | |
pm2.meta_value as license_key | |
FROM wp_edd_customers AS edd_customers | |
LEFT JOIN wp_edd_subscriptions AS edd_subs | |
ON edd_subs.customer_id = edd_customers.id | |
LEFT JOIN wp_posts AS posts | |
ON payment_ids = posts.ID | |
LEFT JOIN wp_postmeta AS pm | |
ON CAST(pm.meta_value AS UNSIGNED) = CAST(payment_ids AS UNSIGNED) AND pm.meta_key = '_edd_sl_payment_id' | |
LEFT JOIN wp_postmeta AS pm2 | |
ON pm.post_id = pm2.post_id AND pm2.meta_key = '_edd_sl_key' AND pm2.meta_value IS NOT NULL | |
WHERE ( edd_subs.status != 'active' OR edd_subs.status IS NULL ) | |
GROUP BY edd_customers.email | |
LIMIT 100 # Dude, this will seriously take *forever* — did you see these JOINs? to postmeta?!! | |
) as q | |
WHERE q.license_key IS NOT NULL |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment