ADO.NET Provider for Adobe Commerce

Build 23.0.8839

Orders

Orders is an auto generated table. The supported operations are Insert, Update, and Querying data from this table.

Select

The Orders table supports server-side processing for all columns in the WHERE clause. Most of these columns support server-side processing for the >, >=, <, <=, =, IN, NOT IN and LIKE operators.

The following examples show the types of queries processed server side.

  • Retrieve all orders from your AdobeCommerce server: 
    SELECT * FROM Orders
  • Retrieve a single order: 
    SELECT * FROM Orders WHERE EntityId = '1'
  • Filter by status: 
    SELECT * FROM Orders WHERE GroupId = 1
  • Filter by created date: 
    SELECT * FROM Orders WHERE CreatedAt > '2017-08-01'
  • Example query with multiple filters: 
    SELECT * FROM Orders WHERE EntityId IN (1, 2) AND CustomerFirstname LIKE '%i%' AND OrderCurrencyCode = 'USD' LIMIT 50

Insert

You can insert items into an order using an INSERT statement. The 'Items' column can be specified using either the temporary table called 'OrderItems#TEMP' or as a simple string aggregate. 'Payment' aggregate column is required for INSERT and UPDATE operations on this table.

  • Using the temporary table: 
    INSERT INTO OrderItems#TEMP (BasePrice, Sku) VALUES (299.99, '8915A40K4')
INSERT INTO OrderItems#TEMP (BasePrice, Sku) VALUES (100, '7115AP0Z8')
INSERT INTO Orders (GrandTotal, Items, Payment) VALUES (399.99, OrderItems#TEMP, '{...your payment aggregate here...}')
  • Using a text aggregate: 
    INSERT INTO Orders (GrandTotal, Items, Payment) VALUES (399.99, '{...Items aggregate here...}', '{...Payment aggregate here...}')

Columns

Name Type ReadOnly Description
AdjustmentNegative Double False

Negative adjustment value.

Id String False

id

AdjustmentPositive Double False

Positive adjustment value.

AppliedRuleIds String False

Applied rule IDs.

BaseAdjustmentNegative Double False

Base negative adjustment value.

BaseAdjustmentPositive Double False

Base positive adjustment value.

BaseCurrencyCode String False

Base currency code.

BaseDiscountAmount Double False

Base discount amount.

BaseDiscountCanceled Double False

Base discount canceled.

BaseDiscountInvoiced Double False

Base discount invoiced.

BaseDiscountRefunded Double False

Base discount refunded.

BaseDiscountTaxCompensationAmount Double False

Base discount tax compensation amount.

BaseDiscountTaxCompensationInvoiced Double False

Base discount tax compensation invoiced.

BaseDiscountTaxCompensationRefunded Double False

Base discount tax compensation refunded.

BaseGrandTotal Double False

Base grand total.

BaseShippingAmount Double False

Base shipping amount.

BaseShippingCanceled Double False

Base shipping canceled.

BaseShippingDiscountAmount Double False

Base shipping discount amount.

BaseShippingDiscountTaxCompensationAmnt Double False

Base shipping discount tax compensation amount.

BaseShippingInclTax Double False

Base shipping including tax.

BaseShippingInvoiced Double False

Base shipping invoiced.

BaseShippingRefunded Double False

Base shipping refunded.

BaseShippingTaxAmount Double False

Base shipping tax amount.

BaseShippingTaxRefunded Double False

Base shipping tax refunded.

BaseSubtotal Double False

Base subtotal.

BaseSubtotalCanceled Double False

Base subtotal canceled.

BaseSubtotalInclTax Double False

Base subtotal including tax.

BaseSubtotalInvoiced Double False

Base subtotal invoiced.

BaseSubtotalRefunded Double False

Base subtotal refunded.

BaseTaxAmount Double False

Base tax amount.

BaseTaxCanceled Double False

Base tax canceled.

BaseTaxInvoiced Double False

Base tax invoiced.

BaseTaxRefunded Double False

Base tax refunded.

BaseToGlobalRate Double False

Base-to-global rate.

BaseToOrderRate Double False

Base-to-order rate.

BaseTotalCanceled Double False

Base total canceled.

BaseTotalDue Double False

Base total due.

BaseTotalInvoiced Double False

Base total invoiced.

BaseTotalInvoicedCost Double False

Base total invoiced cost.

BaseTotalOfflineRefunded Double False

Base total offline refunded.

BaseTotalOnlineRefunded Double False

Base total online refunded.

BaseTotalPaid Double False

Base total paid.

BaseTotalQtyOrdered Double False

Base total quantity ordered.

BaseTotalRefunded Double False

Base total refunded.

BillingAddress String False

This is an auto-generated column

BillingAddressId Int False

Billing address ID.

CanShipPartially Int False

Can-ship-partially flag value.

CanShipPartiallyItem Int False

Can-ship-partially-item flag value.

CouponCode String False

Coupon code.

CreatedAt Datetime False

Created-at timestamp.

CustomerDob String False

Customer date-of-birth (DOB).

CustomerEmail String False

Customer email address.

CustomerFirstname String False

Customer first name.

CustomerGender Int False

Customer gender.

CustomerGroupId Int False

Customer group ID.

CustomerId Int False

Customer ID.

CustomerIsGuest Int False

Customer-is-guest flag value.

CustomerLastname String False

Customer last name.

CustomerMiddlename String False

Customer middle name.

CustomerNote String False

Customer note.

CustomerNoteNotify Int False

Customer-note-notify flag value.

CustomerPrefix String False

Customer prefix.

CustomerSuffix String False

Customer suffix.

CustomerTaxvat String False

Customer value-added tax (VAT).

DiscountAmount Double False

Discount amount.

DiscountCanceled Double False

Discount canceled.

DiscountDescription String False

Discount description.

DiscountInvoiced Double False

Discount invoiced.

DiscountRefunded Double False

Discount refunded amount.

DiscountTaxCompensationAmount Double False

Discount tax compensation amount.

DiscountTaxCompensationInvoiced Double False

Discount tax compensation invoiced amount.

DiscountTaxCompensationRefunded Double False

Discount tax compensation refunded amount.

EditIncrement Int False

Edit increment value.

EmailSent Int False

Email-sent flag value.

EntityId [KEY] Int False

Order ID.

ExtCustomerId String False

External customer ID.

ExtensionAttributes String False

This is an auto-generated column

ExtOrderId String False

External order ID.

ForcedShipmentWithInvoice Int False

Forced-shipment-with-invoice flag value.

GlobalCurrencyCode String False

Global currency code.

GrandTotal Double False

Grand total.

HoldBeforeState String False

Hold before state.

HoldBeforeStatus String False

Hold before status.

IncrementId String False

Increment ID.

IsVirtual Int False

Is-virtual flag value.

Items String False

Array of items.

OrderCurrencyCode String False

Order currency code.

OriginalIncrementId String False

Original increment ID.

Payment String False

This is an auto-generated column

PaymentAuthExpiration Int False

Payment authorization expiration date.

PaymentAuthorizationAmount Double False

Payment authorization amount.

ProtectCode String False

Protect code.

QuoteAddressId Int False

Quote address ID.

QuoteId Int False

Quote ID.

RelationChildId String False

Relation child ID.

RelationChildRealId String False

Relation child real ID.

RelationParentId String False

Relation parent ID.

RelationParentRealId String False

Relation parent real ID.

RemoteIp String False

Remote IP address.

SearchCriteria String False

This is an auto-generated column

ShippingAmount Double False

Shipping amount.

ShippingCanceled Double False

Shipping canceled amount.

ShippingDescription String False

Shipping description.

ShippingDiscountAmount Double False

Shipping discount amount.

ShippingDiscountTaxCompensationAmount Double False

Shipping discount tax compensation amount.

ShippingInclTax Double False

Shipping including tax amount.

ShippingInvoiced Double False

Shipping invoiced amount.

ShippingRefunded Double False

Shipping refunded amount.

ShippingTaxAmount Double False

Shipping tax amount.

ShippingTaxRefunded Double False

Shipping tax refunded amount.

State String False

State.

Status String False

Status.

StatusHistories String False

Array of status histories.

StoreCurrencyCode String False

Store currency code.

StoreId Int False

Store ID.

StoreName String False

Store name.

StoreToBaseRate Double False

Store-to-base rate.

StoreToOrderRate Double False

Store-to-order rate.

Subtotal Double False

Subtotal.

SubtotalCanceled Double False

Subtotal canceled amount.

SubtotalInclTax Double False

Subtotal including tax amount.

SubtotalInvoiced Double False

Subtotal invoiced amount.

SubtotalRefunded Double False

Subtotal refunded amount.

TaxAmount Double False

Tax amount.

TaxCanceled Double False

Tax canceled amount.

TaxInvoiced Double False

Tax invoiced amount.

TaxRefunded Double False

Tax refunded amount.

TotalCanceled Double False

Total canceled.

TotalCount Int False

Total count.

TotalDue Double False

Total due.

TotalInvoiced Double False

Total invoiced amount.

TotalItemCount Int False

Total item count.

TotalOfflineRefunded Double False

Total offline refunded amount.

TotalOnlineRefunded Double False

Total online refunded amount.

TotalPaid Double False

Total paid.

TotalQtyOrdered Double False

Total quantity ordered.

TotalRefunded Double False

Total amount refunded.

UpdatedAt Datetime False

Updated-at timestamp.

Weight Double False

Weight.

XForwardedFor String False

X-Forwarded-For field value.

Copyright (c) 2024 CData Software, Inc. - All rights reserved.
Build 23.0.8839