Products

Products is an auto generated table

Select

Custom Attributes will be dynamically retrieved as columns when querying this table. The Products 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 driver will use the Magento API to filter the results with these operators while the rest of the filter is executed client side within the provider. You can turn off client-side execution by setting SupportEnhancedSQL to false in which case any search criteria that invokes other operators will cause an error.

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

• Retrieve all products from your Magento server:\

  SELECT * FROM Products

• Retrieve a single product:\

  SELECT * FROM Products WHERE Sku = 'sjl-8082'

• Filter by status:\

  SELECT * FROM Products WHERE Status = 1

• Filter by created date:\

  SELECT * FROM Products WHERE CreatedAt > '2017-08-01'

• Example query with multiple filters:\

  SELECT * FROM Products WHERE Id IN (250,260) AND Name LIKE '%d%' AND Visibility = 4 LIMIT 50

You can turn off client-side execution by setting SupportEnhancedSQL to false, in which case any search criteria that refers to any other columns will cause an error.

Insert

To create a new product, Name, AttributeSetId, and Sku are required.\

INSERT INTO Products(Sku, AttributeSetId, Name) VALUES ('sjl-1999', '4', 'insert prod')

Update

To update a product, you must set the Sku and Price columns.\

UPDATE Products SET Price = '88', CustomAttribute1='ValidValue' WHERE Sku = 'sjl-1999'

Delete

Products can be deleted by providing the Sku column.\

DELETE FROM Products WHERE Sku = 'sjl-1999'

Columns

Name	Type	ReadOnly	Description
Id	Int	False	Id
AttributeSetId	Int	False	Attribute set id
CreatedAt	Datetime	False	Created date
ExtensionAttributes	String	False	This is an auto-generated column
MediaGalleryEntries	String	False	Media gallery entries
Name	String	False	Name
Options	String	False	List of product options
Price	String	False	Price
ProductLinks	String	False	Product links info
Sku [KEY]	String	False	Sku
Status	Int	False	Status
TierPrices	String	False	List of product tier prices
TypeId	String	False	Type id
UpdatedAt	Datetime	False	Updated date
Visibility	Int	False	Visibility
Weight	String	False	Weight
CustomAttribute1	String	False	A custom attribute created for the Products. The custom fields will be listed dynamically.
CustomAttribute2	String	False	A custom attribute created for the Products. The custom fields will be listed dynamically.

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
Sku_p	String	This is an auto-generated column