Woocommerce Subscription SQL Query WordPress

• REFUNDED

Hi,

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 : https://docs.woocommerce.com/document/subscriptions/develop/data-structure/

Answers (2)

2018-07-28

Arnav Joy answers:

What is URL of your site?

---

gorbatch comments:

Hi,

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

2018-07-28

Bob answers:

you can achieve with meta query.

https://codex.wordpress.org/Class_Reference/WP_Meta_Query
https://codex.wordpress.org/Class_Reference/WP_Query#Custom_Field_Parameters

Here is not tested code with rough idea.

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