SmartCollections
Query, insert, update, or delete information regarding different smart collections.
Table-Specific Information
Select
The driver 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 driver processes other filters client-side within the driver. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.
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). |