MCP Server for WooCommerce

Build 25.0.9440

Subscriptions

The Subscriptions table enables you to create, view, update, and delete individual, or a batch of subscriptions. This table shows data only if the WooCommerce Subscriptions Plugin is enabled.

Table Specific Information

Select

WooCommerce supports server-side filtering only on a limited set of columns in the Subscriptions table. All other filters are applied client-side. The following columns support server-side filtering with the specified operators:

  • Id supports the '=' operator.
  • ParentId supports the '=', '!=', 'IN', and 'NOT IN' operators.
  • CustomerId supports the '=' operator.
  • Status supports the '=' operator.
  • Product supports the '=' operator.

The following examples demonstrate queries processed server-side: 

-- Filter by subscription ID
SELECT * FROM Subscriptions WHERE Id = 4

-- Filter by parent subscription
SELECT * FROM Subscriptions WHERE ParentId = 4

-- Exclude a specific parent
SELECT * FROM Subscriptions WHERE ParentId != 4

-- Match any of multiple parents
SELECT * FROM Subscriptions WHERE ParentId IN (4, 5)

-- Exclude multiple parents
SELECT * FROM Subscriptions WHERE ParentId NOT IN (4, 5)

-- Filter by status and customer
SELECT * FROM Subscriptions WHERE Status = 'pending' AND CustomerId = 501

-- Filter by product ID
SELECT * FROM Subscriptions WHERE Product = 501

Insert

No columns are required for basic insert operations on the Subscriptions table.

To insert values into aggregate columns such as LineItems, use a temporary table or pass the value as a JSON-formatted string. 

-- Create a temporary table for line items
INSERT INTO SubscriptionLineItems#TEMP (ProductId, ReferenceNumber) VALUES (37, 1)

-- Insert a subscription referencing the temporary table
INSERT INTO Subscriptions#TEMP (CustomerId, LineItems, ReferenceNumber) 
VALUES ('109533', 'SubscriptionLineItems#TEMP', 1)

-- Commit the data to the Subscriptions table
INSERT INTO Subscriptions (CustomerId, LineItems) 
SELECT CustomerId, LineItems FROM Subscriptions#TEMP

Update

To update aggregate columns that contain object arrays, use a temporary table or a JSON-formatted string, as described above in the Insert section.

To add or update metadata, use the Metadata column. If a key does not exist, it is created. If it already exists, its value is updated. 

-- Add or update metadata fields
UPDATE Subscriptions 
SET Metadata = 'key1:val1, key2:val2' 
WHERE Id = 120

Delete

Delete operations are not supported for the Subscriptions table.

To delete a subscription, use the DeleteSubscription stored procedure.

Columns

Name Type ReadOnly References Description
Id [KEY] Integer True

Unique identifier for the resource.

ParentId Integer False

Parent/initial order ID for the subscription.

BillingAddress1 String False

Billing Address line 1.

BillingAddress2 String False

Billing Address line 2.

BillingCity String False

Billing City.

BillingCompany String False

Billing Company.

BillingCountry String False

Billing Country.

BillingEmail String False

Billing Email.

BillingFirstName String False

Billing First Name.

BillingLastName String False

Billing Last Name.

BillingPhone String False

Billing Phone.

BillingPostcode String False

Billing Postcode.

BillingState String False

Billing State.

BillingInterval Integer False

The number of billing periods between subscription renewals.

BillingPeriod String False

The subscription's billing period.

The allowed values are day, week, month, year.

CancelledDateGmt Datetime False

The date the subscription was cancelled in GMT. In a write context, it must be the format YYYY-mm-dd H:i:s

CartTax String True

Sum of line item taxes only.

CouponLines String False

Coupons line data.

CreatedVia String True

Where the subscription was created.

Currency String False

Currency the subscription was created with, in ISO format.

CustomerId Integer False

Customers.Id

User ID who owns the subscription.

CustomerIpAddress String True

Customer's IP address.

CustomerNote String False

Note left by customer during checkout.

CustomerUserAgent String True

User agent of the customer.

DateCompleted Datetime True

The date the subscription was completed, in the site's timezone.

DateCompletedGmt Datetime False

The date the subscription was completed, as GMT.

DateCreated Datetime True

The date the subscription was created, in the site's timezone.

DateCreatedGmt Datetime False

The date the subscription was created, as GMT.

DateModified Datetime True

The date the subscription was last modified, in the site's timezone.

DateModifiedGmt Datetime False

The date the subscription was modified, as GMT.

DatePaid Datetime True

The date the subscription has been paid, in the site's timezone.

DatePaidGmt Datetime False

The date the subscription was paid, as GMT.

DiscountTax String True

Total discount tax amount for the subscription.

DiscountTotal String True

Total discount amount for the subscription.

EndDateGmt Datetime False

The subscription's trial end date in GMT.

FeeLines String False

Fee lines data.

IsEditable Boolean True

Is editable.

LastPaymentDateGmt Datetime False

The subscription's last parent, renewal or switch order date in GMT.

LineItems String False

Line items data.

MetaData String False

Meta data.

NeedsPayment Boolean True

Needs payment.

NeedsProcessing Boolean True

Needs processing.

NextPaymentDateGmt Datetime False

The subscription's next payment date in GMT.

Number String False

The subscription's number.

OrderKey String True

Order key.

PaymentMethod String False

Payment method ID.

PaymentMethodTitle String False

Payment method title.

PaymentRetryDateGmt Datetime True

The subscription's payment retry date in GMT.

PaymentUrl String True

Payment URL.

PricesIncludeTax Boolean True

Shows if the prices included tax during checkout.

RemovedLineItems String True

Item data for items removed by the customer from their my account page.

RequiresManualRenewal Boolean True

Requires manual renewal.

ResubscribedFrom String True

The ID of the subscription this subscription was resubscribed from.

ResubscribedSubscription String True

The ID of the new resubscribed subscription.

ShippingAddress1 String False

Address line 1.

ShippingAddress2 String False

Address line 2.

ShippingCity String False

City name.

ShippingCompany String False

Company name.

ShippingCountry String False

Country code in ISO 3166-1 alpha-2 format.

ShippingFirstName String False

First name.

ShippingLastName String False

Last name.

ShippingPhone String False

Phone number.

ShippingPostcode String False

Postal code.

ShippingState String False

ISO code or name of the state, province or district.

ShippingLines String False

Shipping lines data.

ShippingTax String False

Total shipping tax amount for the subscription.

ShippingTotal String False

Total shipping amount for the subscription.

StartDateGmt Datetime True

The subscription's start date in GMT.

Status String False

Subscription status.

The default value is pending.

SuspensionCount Integer True

Suspension count.

TaxLines String True

Tax lines data.

Total String True

Grand total.

TotalTax String True

Sum of all taxes.

TrialEndDateGmt Datetime False

The subscription's trial end date in GMT.

Version String True

Version of WooCommerce when the subscription was made.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name Type Description
TransitionStatus String

The subscription status to transition to. This will work only for Insert.

PaymentDetails String

Payment method data. This will work only for Update and Insert.

Product String

Limit result set to subscriptions assigned a specific product ID.

ReferenceNumber String

This column will be used in Bulk operations to get specific values from the Temp tables.

Copyright (c) 2025 CData Software, Inc. - All rights reserved.
Build 25.0.9440