Ask your WordPress questions! Pay money and get answers fast! Comodo Trusted Site Seal
Official PayPal Seal

Woocommerce Subscription SQL Query WordPress



I have a Woocommerce store with Woocommerce Subscriptions.
Subscriptions are listed in the table wp_posts, in this table :
- the column ID indicate the subscription ID (only when line in this table can have this ID)
- the column post_status indicate their status (wc-active, wc-pending-cancel, ...)

Metadata about the subscriptions (like the date of a subscription ending) are in the table wp_postmeta, in this table :
- the column post_id is the ID of the subscription (= ID of wp_posts). There is multiple line with the same post_id, there is one line per meta_key. We care only about 1 meta key "_schedule_end"
This meta_key has a meta_value that can be either a date ("2018-09-01 01:00:00") or 0 if the end of the trial hasnt been decided yet

I would like to have a mysql query to count :
the number of active subscriptions ( post_status=wc-active ) minus the ones with "_schedule_end" BEFORE August 1st 2018 (but with the one where the meta_value is 0
+ the number of pending-cancel subscriptions ( post_status=wc-pending-cancel ) with "_schedule_end" AFTER August 1st 2018

Hope I have been clear, this is the normal data relation of WC Subcription, here is the documentation :

Answers (2)


Arnav Joy answers:

What is URL of your site?

gorbatch comments:


Why? I dont think you need it, I want a generic mysql query :)


Bob answers:

you can achieve with meta query.

Here is not tested code with rough idea.

$args = array(
'post_status' => array( 'wc-active', 'wc-pending-cancel ' ),
'meta_query' => array(
'relation' => 'OR',
'key' => '_schedule_end',
'value' => 0,
'compare' => '=',
'key' => '_schedule_end',
'value' => '2018-08-01 01:00:00',
'compare' => '>',
$query = new WP_Query( $args )