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	References	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.
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.
PurchaseNote	String	False		Optional note to send the customer after purchase.
CategoriesAggregate	String	False		List of categories.
TagsAggregate	String	False		List of tags.
ImagesAggregate	String	False		List of images.
AttributesAggregate	String	False		List of attributes.
DefaultAttributesAggregate	String	False		Defaults variation attributes.
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.
DimensionsLength	String	False		Product length.
DimensionsWidth	String	False		Product width.
DimensionsHeight	String	False		Product height.

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.