Skip to content

Instantly share code, notes, and snippets.

@jchristopher

jchristopher/my.sql

Last active October 24, 2017 00:27
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save jchristopher/d1f4d506b3f272488d91107899f7ab9b to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save jchristopher/d1f4d506b3f272488d91107899f7ab9b to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. jchristopher revised this gist Apr 13, 2017. No changes.
  2. jchristopher revised this gist Apr 13, 2017. 1 changed file with 1 addition and 0 deletions.
    1 change: 1 addition & 0 deletions my.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,3 +1,4 @@
    # THIS IS NOT TESTED ENOUGH !!!!!!!!!!!!!!!!!! EXPECT INACCURACIES !!!!!!!!!!!!!!!!!!
    # THIS IS CRAZY CRAZY SLOW
    # ADJUST THE LIMIT ON LINE 20 ACCORDINGLY (READ: IF YOU DARE)
    SELECT *
  3. jchristopher revised this gist Apr 13, 2017. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion my.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -17,6 +17,6 @@ FROM (
    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*
    LIMIT 100 # Dude, this will seriously take *forever* — did you see these JOINs? to postmeta?!!
    ) as q
    WHERE q.license_key IS NOT NULL
  4. jchristopher revised this gist Apr 13, 2017. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion my.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -17,6 +17,6 @@ FROM (
    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 1000
    LIMIT 100 # Dude, this will seriously take *forever*
    ) as q
    WHERE q.license_key IS NOT NULL
  5. jchristopher revised this gist Apr 13, 2017. 1 changed file with 9 additions and 7 deletions.
    16 changes: 9 additions & 7 deletions my.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,17 +1,19 @@
    # THIS IS CRAZY CRAZY SLOW
    # ADJUST THE LIMIT ON LINE 18 ACCORDINGLY (READ: IF YOU DARE)
    # ADJUST THE LIMIT ON LINE 20 ACCORDINGLY (READ: IF YOU DARE)
    SELECT *
    FROM (
    SELECT edd_customers.email AS email,
    SELECT
    SUBSTRING_INDEX(SUBSTRING_INDEX(edd_customers.name, ' ', 1), ' ', -1) as firstname,
    edd_customers.email AS email,
    pm2.meta_value as license_key
    FROM swp_edd_customers AS edd_customers
    LEFT JOIN swp_edd_subscriptions AS edd_subs
    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 swp_posts AS posts
    LEFT JOIN wp_posts AS posts
    ON payment_ids = posts.ID
    LEFT JOIN swp_postmeta AS pm
    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 swp_postmeta AS pm2
    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
  6. jchristopher created this gist Apr 12, 2017.
    20 changes: 20 additions & 0 deletions my.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,20 @@
    # THIS IS CRAZY CRAZY SLOW
    # ADJUST THE LIMIT ON LINE 18 ACCORDINGLY (READ: IF YOU DARE)
    SELECT *
    FROM (
    SELECT edd_customers.email AS email,
    pm2.meta_value as license_key
    FROM swp_edd_customers AS edd_customers
    LEFT JOIN swp_edd_subscriptions AS edd_subs
    ON edd_subs.customer_id = edd_customers.id
    LEFT JOIN swp_posts AS posts
    ON payment_ids = posts.ID
    LEFT JOIN swp_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 swp_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 1000
    ) as q
    WHERE q.license_key IS NOT NULL