TDV Adapter for WooCommerce

Build 22.0.8462

Products

Retrieve and modify products.

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, Slug, Type, Status, Sku, Price, OnSale, TaxClass, StockStatus, ShippingClass, ParentId. The price filter can be used with the <, > conditions and may be used twice to specify a range. All other columns are processed client side. 

SELECT * FROM Products WHERE Id = 4
SELECT * FROM Products WHERE Slug = 'criteria' AND OnSale = true
SELECT * FROM Products WHERE Price < 14.99 AND Price > 11.99

Update

To perform an update or insert using any of the aggregate columns which contain simple primitive arrays, we can simply pass a JSON array string as the value: 

UPDATE Products SET Name = 'ProductName', RelatedIdsAggregate = '[14, 16, 29]' WHERE ID = 42

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 ProductCategories#TEMP (Name) VALUES ('Category')
INSERT INTO ProductCategories#TEMP (Name) VALUES ('Category2')
UPDATE Products SET CategoriesAggregate = 'ProductCategories#TEMP' WHERE ID = 167

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 Products 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 Description
Id [KEY] Long True

Unique identifier for the resource.

Name String False

Product name.

Slug String False

Product slug.

Permalink String True

Product URL.

DateCreated Datetime True

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

DateModified Datetime True

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

Type String False

Product type. Options: simple, grouped, external and variable. Defaults to simple.

Status String False

Product status (post status). Options: draft, pending, private and publish. Defaults to publish.

Featured Boolean False

Featured product. Defaults to false.

CatalogVisibility String False

Catalog visibility. Options: visible, catalog, search and hidden. Defaults to visible.

Description String False

Product description.

ShortDescription String False

Product short description.

Sku String False

Unique identifier.

Price String True

Current product price.

RegularPrice String False

Product regular price.

SalePrice String False

Product sale price.

DateOnSaleFrom Datetime False

Start date of sale price, in the site's timezone.

DateOnSaleTo Datetime False

End date of sale price, in the site's timezone.

PriceHtml String True

Price formatted in HTML.

OnSale Boolean True

Shows if the product is on sale.

Purchasable Boolean True

Shows if the product can be bought.

TotalSales Integer True

Amount of sales.

Virtual Boolean False

If the product is virtual. Defaults to false.

Downloadable Boolean False

If the product is downloadable. Defaults to false.

Downloads String False

List of downloadable files. See Product - Downloads properties

DownloadLimit Integer False

Number of times downloadable files can be downloaded after purchase. Defaults to -1.

DownloadExpiry Integer False

Number of days until access to downloadable files expires. Defaults to -1.

ExternalUrl String False

Product external URL. Only for external products.

ButtonText String False

Product external button text. Only for external products.

TaxStatus String False

Tax status. Options: taxable, shipping and none. Defaults to taxable.

TaxClass String False

Tax class.

ManageStock Boolean False

Stock management at product level. Defaults to false.

StockQuantity Integer False

Stock quantity.

StockStatus String False

Controls the stock status of the product. Options: instock, outofstock, onbackorder. Defaults to instock.

Backorders String False

If managing stock, this controls if backorders are allowed. Options: no, notify and yes. Defaults to no.

BackordersAllowed Boolean True

Shows if backorders are allowed.

Backordered Boolean True

Shows if the product is on backordered.

SoldIndividually Boolean False

Allow one item to be bought in a single order. Defaults to false.

Weight String False

Product weight.

ShippingRequired Boolean True

Shows if the product need to be shipped.

ShippingTaxable Boolean True

Shows whether or not the product shipping is taxable.

ShippingClass String False

Shipping class slug.

ShippingClassId Long True

Shipping class ID.

ReviewsAllowed Boolean False

Allow reviews. Defaults to true.

AverageRating String True

Reviews average rating.

RatingCount Integer True

Amount of reviews that the product have.

RelatedIdsAggregate String True

List of related products IDs.

UpsellIdsAggregate String False

List of up-sell products IDs.

CrossSellIdsAggregate String False

List of cross-sell products IDs.

ParentId Long False

Product parent ID.

PurchaseNoteAggregate String False

Optional note to send the customer after purchase.

CategoriesAggregate String False

List of categories. See Product - Categories properties

TagsAggregate String False

List of tags. See Product - Tags properties

ImagesAggregate String False

List of images. See Product - Images properties

AttributesAggregate String False

List of attributes. See Product - Attributes properties

DefaultAttributesAggregate String False

Defaults variation attributes. See Product - Default attributes properties

VariationsAggregate String True

List of variations IDs.

GroupedProductsAggregate String False

List of grouped products ID.

MenuOrder Integer False

Menu order, used to custom sort products.

MetaData String False

Meta data. See Product - Meta data properties

DimensionsLength String False

Product length.

DimensionsWidth String False

Product width.

DimensionsHeight String False

Product height.

Copyright (c) 2023 CData Software, Inc. - All rights reserved.
Build 22.0.8462