SmartCollections

Query, insert, update, or delete information regarding different smart collections.

Table-Specific Information

Select

The adapter uses the Shopify API to process search criteria that refer to the Id, ProductId, Title, Handle, PublishedStatus, UpdatedAt, PublishedAt columns. The supported SQL operators are '=' and 'IN' for Id, '=' for the ProductId, Title, Handle, and PublishedStatus columns and '>' and '<' for the UpdatedAt and PublishedAt columns. The adapter processes other filters client-side within the adapter.

For example, the following queries are processed server side.

SELECT * FROM SmartCollections

SELECT * FROM SmartCollections WHERE Id='123'

SELECT * FROM SmartCollections WHERE Id IN ('123','456')

SELECT * FROM SmartCollections WHERE ProductId='123'

SELECT * FROM SmartCollections WHERE Title='Ducks'

SELECT * FROM SmartCollections WHERE Handle='frontpage'

SELECT * FROM SmartCollections WHERE PublishedStatus='published'

SELECT * FROM SmartCollections WHERE UpdatedAt > '2018-05-02'

SELECT * FROM SmartCollections WHERE PublishedAt < '2017-08-15'

Insert

You must specify the Title and Rules to create a smart collection. For example:

INSERT INTO SmartCollections (Title, Rules) VALUES ('IPods', '[{"column": "vendor","relation": "equals","condition": "Apple"}]')

Update

You must specify the smart collection Id to update a smart collection. For example:

UPDATE SmartCollections SET BodyHtml='5000 songs in your pocket' WHERE Id='123'

Delete

You must specify the Id of the smart collection to delete it.

DELETE FROM SmartCollections WHERE Id ='123'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Long	True		The ID of the smart collection.
Title	String	False		The name of the smart collection. Maximum length: 255 characters.
BodyHtml	String	False		The description of the smart collection. Includes HTML markup. Many shop themes display this on the smart collection page.
Image	String	False		A JSON aggregate with information regarding the image of smart collection.
Rules	String	False		A JSON aggregate with the list of rules that define what products go into the smart collection. Valid values for each rule field: column: tag, title, type, vendor, variant_price, variant_compare_at_price, variant_weight, variant_inventory, variant_title; relation: equals, greater_than, less_than, starts_with, ends_with, contains; condition: any string
Handle	String	False		A human-friendly unique string for the smart collection. Automatically generated from the title. Used in shop themes by the Liquid templating language to refer to the smart collection. (maximum: 255 characters)
Disjunctive	Boolean	False		Whether the product must match all the rules to be included in the smart collection. Valid values: true: products only need to match one or more of the rules to be included in the smart collection, false: products must match all of the rules to be included in the smart collection.
SortOrder	String	False		The order in which products in the smart collection appear.
TemplateSuffix	String	False		The suffix of the Liquid template that the shop uses. By default, the original template is called product.liquid, and additional templates are called product.suffix.liquid.
PublishedScope	String	False		Whether the smart collection is published to Point of Sale. Valid values: web: The smart collection is published to the shop's online channels and not published to the Point of Sale channel, global: The smart collection is published to both the online channels and the Point of Sale channel.
UpdatedAt	Datetime	True		The date and time when the smart collection was last modified.
PublishedAt	Datetime	True		The date and time when the smart collection was published. Returns null when the collection is hidden.

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
ProductId	String	Show smart collections that include a given product.
PublishedStatus	String	Show smart collection with a given published status: published: show only published smart collections, unpublished: show only unpublished smart collections, any: show all smart collections. (default: any).

TDV Adapter for Shopify