Orders

Retrieve and modify orders.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available columns for this table are: Id, ParentId, Status, ProductId. All other columns are processed client side.

SELECT * FROM Orders WHERE Id = 4
SELECT * FROM Orders WHERE ParentId = 4
SELECT * FROM Orders WHERE Status = 'pending' AND CustomerId = 501

Update

To perform an update or insert using any of the aggregate columns which contain object arrays, we can use a temporary table, or pass the value as a JSON string:

INSERT INTO OrderCouponLines#TEMP (Code) VALUES ('CouponCode')
UPDATE Orders SET CouponLinesAggregate = 'OrderCouponLines#TEMP' WHERE ID = 14

To introduce new metadata fields which are not present in the schema, the 'metadata' pseudocolumn can be used. The update below will create two new metadata fields with keys 'key1' and 'key2' and set their respective values. If any of the keys specified already exists, its value will be updated.

UPDATE Orders SET metadata = 'key1:val1, key2:val2' WHERE ID = 58

Insert

This table does not have any required attributes when performing an insert.

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	Long	True		Unique identifier for the resource.
ParentId	Long	False		Parent order ID.
Number	String	True		Order number.
OrderKey	String	True		Order key.
CreatedVia	String	True		Shows where the order was created.
Version	String	True		Version of WooCommerce which last updated the order.
Status	String	False		Order status. Options: pending, processing, on-hold, completed, cancelled, refunded, failed and trash. Defaults to pending.
Currency	String	False		Currency the order was created with, in ISO format. Options: AED, AFN, ALL, AMD, ANG, AOA, ARS, AUD, AWG, AZN, BAM, BBD, BDT, BGN, BHD, BIF, BMD, BND, BOB, BRL, BSD, BTC, BTN, BWP, BYR, BZD, CAD, CDF, CHF, CLP, CNY, COP, CRC, CUC, CUP, CVE, CZK, DJF, DKK, DOP, DZD, EGP, ERN, ETB, EUR, FJD, FKP, GBP, GEL, GGP, GHS, GIP, GMD, GNF, GTQ, GYD, HKD, HNL, HRK, HTG, HUF, IDR, ILS, IMP, INR, IQD, IRR, IRT, ISK, JEP, JMD, JOD, JPY, KES, KGS, KHR, KMF, KPW, KRW, KWD, KYD, KZT, LAK, LBP, LKR, LRD, LSL, LYD, MAD, MDL, MGA, MKD, MMK, MNT, MOP, MRO, MUR, MVR, MWK, MXN, MYR, MZN, NAD, NGN, NIO, NOK, NPR, NZD, OMR, PAB, PEN, PGK, PHP, PKR, PLN, PRB, PYG, QAR, RON, RSD, RUB, RWF, SAR, SBD, SCR, SDG, SEK, SGD, SHP, SLL, SOS, SRD, SSP, STD, SYP, SZL, THB, TJS, TMT, TND, TOP, TRY, TTD, TWD, TZS, UAH, UGX, USD, UYU, UZS, VEF, VND, VUV, WST, XAF, XCD, XOF, XPF, YER, ZAR and ZMW. Defaults to USD.
DateCreated	Datetime	True		The date the order was created, in the site's timezone.
DateModified	Datetime	True		The date the order was last modified, in the site's timezone.
DiscountTotal	String	True		Total discount amount for the order.
DiscountTax	String	True		Total discount tax amount for the order.
ShippingTotal	String	True		Total shipping amount for the order.
ShippingTax	String	True		Total shipping tax amount for the order.
CartTax	String	True		Sum of line item taxes only.
Total	String	True		Grand total.
TotalTax	String	True		Sum of all taxes.
PricesIncludeTax	Boolean	True		True the prices included tax during checkout.
CustomerId	Long	False		User ID who owns the order. 0 for guests. Defaults to 0.
CustomerIpAddress	String	True		Customer's IP address.
CustomerUserAgent	String	True		User agent of the customer.
CustomerNote	String	False		Note left by customer during checkout.
PaymentMethod	String	False		Payment method ID.
PaymentMethodTitle	String	False		Payment method title.
TransactionId	String	False		Unique transaction ID.
DatePaid	Datetime	True		The date the order was paid, in the site's timezone.
DateCompleted	Datetime	True		The date the order was completed, in the site's timezone.
CartHash	String	True		MD5 hash of cart items to ensure orders are not modified.
LineItemsAggregate	String	False		Line items data. See Order - Line items properties
TaxLinesAggregate	String	True		Tax lines data.
ShippingLinesAggregate	String	False		Shipping lines data.
FeeLinesAggregate	String	False		Fee lines data.
CouponLinesAggregate	String	False		Coupons line data.
RefundsAggregate	String	True		List of refunds.
SetPaid	Boolean	False		Define if the order is paid. It will set the status to processing and reduce stock items. Defaults to false. write-only
BillingFirstName	String	False		First name.
BillingLastName	String	False		Last name.
BillingCompany	String	False		Company name.
BillingAddress1	String	False		Address line 1
BillingAddress2	String	False		Address line 2
BillingCity	String	False		City name.
BillingState	String	False		ISO code or name of the state, province or district.
BillingPostcode	String	False		Postal code.
BillingCountry	String	False		Country code in ISO 3166-1 alpha-2 format.
BillingEmail	String	False		Email address.
BillingPhone	String	False		Phone number.
ShippingFirstName	String	False		First name.
ShippingLastName	String	False		Last name.
ShippingCompany	String	False		Company name.
ShippingAddress1	String	False		Address line 1
ShippingAddress2	String	False		Address line 2
ShippingCity	String	False		City name.
ShippingState	String	False		ISO code or name of the state, province or district.
ShippingPostcode	String	False		Postal code.
ShippingCountry	String	False		Country code in ISO 3166-1 alpha-2 format.
Metadata	String	False		Meta data. See Order - Meta data properties.
ProductId	Long	False		Limit result set to resources assigned to a specific product.

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
ReferenceNumber	String	This column will be used in Bulk operations to get specific values from the Temp tables.