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
SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。
Name | Type | Description |
ReferenceNumber | String |
This column will be used in Bulk operations to get specific values from the Temp tables. |