Skip to content

Instantly share code, notes, and snippets.

@kjohnson
Last active October 20, 2023 03:38
Show Gist options
  • Save kjohnson/bdc3544800bdd830ddb4ab8d880fc7ca to your computer and use it in GitHub Desktop.
Save kjohnson/bdc3544800bdd830ddb4ab8d880fc7ca to your computer and use it in GitHub Desktop.
Query WooCommerce Subscriptions with Next Payment and Last Payment
<?php
global $wpdb;
return "
SELECT
ShopOrder.ID as order_id,
ShopOrder.post_parent as parent_id,
ShopOrder.post_date,
ShopOrder.post_status,
OrderTotal.meta_value as order_total,
User.ID as user_id,
User.user_email,
User.display_name as user_display_name,
NextPaymentMeta.meta_value as next_payment_date,
max(RelatedOrders.post_date) as last_payment_date
FROM {$wpdb->prefix}posts as ShopOrder
JOIN {$wpdb->prefix}postmeta as OrderTotal
ON ShopOrder.ID = OrderTotal.post_id
JOIN {$wpdb->prefix}postmeta as CustomerMeta
ON ShopOrder.ID = CustomerMeta.post_id
JOIN {$wpdb->prefix}users as User
ON CustomerMeta.meta_value = User.ID
JOIN {$wpdb->prefix}postmeta as NextPaymentMeta
ON ShopOrder.ID = NextPaymentMeta.post_id
JOIN {$wpdb->prefix}postmeta as RenewalsMeta
ON RenewalsMeta.meta_value = ShopOrder.ID
JOIN {$wpdb->prefix}posts as RelatedOrders
ON RelatedOrders.ID = RenewalsMeta.post_id
WHERE ShopOrder.post_type = 'shop_subscription'
AND ShopOrder.post_status IN ( 'wc-active' )
AND OrderTotal.meta_key = '_order_total'
AND CustomerMeta.meta_key = '_customer_user'
AND NextPaymentMeta.meta_key = '_schedule_next_payment'
AND RenewalsMeta.meta_key = '_subscription_renewal'
GROUP BY ShopOrder.ID
";
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment