Feed query language

A feed’s records come from a query: a base object plus four optional clauses. The clauses use SQL syntax but are not SQL. They are parsed, checked against the fields that actually exist, and rebuilt before they reach the database, so nothing written here is passed through as a SQL string.

Part	Field on Query	Purpose
Object	s_c__Object_API_Name__c	The base object to select from, as a Salesforce API name such as product2 or s_c__article__c
from	s_c__From__c	Declares related objects to join, each with an alias and a match condition
where	s_c__Where__c	Filters which records are returned
order	s_c__Order__c	Orders them
select	s_c__Select__c	Optional. Projects a specific set of columns instead of whole records

A query with only an object returns every record of that object.

Each part is a separate field on the Query record (s_c__Query__c), not one statement typed in one place. A field holds its clause body without the leading keyword, so Where holds s_c__active__c = true, not WHERE s_c__active__c = true. From is the exception: it holds the join clauses themselves, each beginning with JOIN, INNER JOIN or LEFT JOIN. Every example below is the contents of one field.

Naming fields

Refer to a field by its Salesforce API name in full, including prefixes and suffixes: s_c__slug__c, name, createddate. Shortened or Ruby-style names are not accepted.

An unqualified name is a field on the base object. A qualified name such as brand.name is a field on a join, where brand is an alias declared in from. Keywords like AND and JOIN are case-insensitive.

An unknown object, unknown field or undeclared alias is rejected loudly. A query is never quietly reduced to an empty result because of a typo.

where

Operator	Meaning
= != > >= < <=	Comparisons
BETWEEN x AND y	Inclusive range
IN (a, b) / NOT IN (a, b)	Membership
LIKE 'pattern' / NOT LIKE 'pattern'	Pattern match, case-sensitive
ILIKE 'pattern' / NOT ILIKE 'pattern'	Pattern match, case-insensitive
IS NULL / IS NOT NULL	Null checks

Combine with AND, OR, NOT and parentheses. Values are single-quoted strings (doubling a quote to escape it), numbers, true or false, and NULL.

```sql

s_c__is_master__c = true AND s_c__active__c = true

s_c__price__c BETWEEN 10 AND 50 AND brand.name LIKE ‘Ac%’

type = ‘physical’ AND (s_c__category__c IN (‘sale’, ‘clearance’) OR s_c__featured__c = true) ```

:::warning A negated comparison does not match null. !=, NOT IN and NOT LIKE leave out rows where the field is empty, which is rarely what the author intended. Add IS NULL explicitly when those rows should be included. :::

Matching regardless of case

Every comparison is case-sensitive by default, so name = 'Red' does not match red.

For pattern matching, use ILIKE instead of LIKE. For everything else, wrap both operands in LOWER() or UPPER():

```sql

LOWER(s_c__brand__c) = LOWER(‘Acme’)

LOWER(s_c__category__c) IN (LOWER(‘Sale’), LOWER(‘Clearance’)) ```

Folding one side only leaves the other side’s case intact and still fails to match, which is the usual mistake. LOWER and UPPER need a text column and are rejected on a number, date or boolean.

from

```sql

[INNER | LEFT] JOIN

Joins default to INNER. Use LEFT to keep base rows with no match, such as a product with no brand. The ON is a match key only, written out in full, and joins on equality; it is not a second filter. An unqualified column in the ON is on the base object.

Several joins are allowed, and a later one may reference an earlier alias to form a chain, which is how a junction object is traversed:

```sql

JOIN account AS brand ON s_c__brand_id__c = brand.sfid

JOIN s_c__products_product_categories__c AS pc ON sfid = pc.s_c__product_id__c JOIN s_c__product_category__c AS cat ON pc.s_c__category_id__c = cat.sfid ```

Aliases must be unique and must not share a name with a base-object field.

To-one and to-many joins behave differently

Which one a join is comes from the ON: the side whose matched column is that table’s own sfid is the “one” side.

• To-one (base.<foreign key> = alias.sfid) matches at most one related row, and can be used in both where and order.
• To-many (base.sfid = alias.<foreign key>) may match many. It can filter in where, compiled as an existence check so base rows are never duplicated, but ordering by it is rejected, because ordering by a one-to-many relationship has no defined answer.

order

```sql

[ASC | DESC] [NULLS FIRST | NULLS LAST] [, ...] ``` Order by a base field or a to-one join field. Ordering by a to-many join field is rejected. To sort regardless of case, wrap the field in `LOWER()`. ## Bindings A clause can read a live value with a `:path` binding, evaluated against the same data a feed template sees. This is what lets one query serve every store: ```sql s_c__store_id__c = :current_store.id ``` A binding may only appear where a value goes: the right side of a comparison, inside `IN`, or as a `BETWEEN` bound. It can never stand in for a field, an alias, a `LIKE` pattern or an ordering term. The value is bound as a parameter rather than pasted into the statement, so a binding cannot be an injection route. A binding is a plain path. No filters, no expressions. It must resolve to a single value; one that resolves to an object, to nothing, or to a list fails loudly, as does a request-specific value used by a pre-built feed, which renders with no request. ### Two reserved bindings | Binding | Resolves to | |---|---| | `:last_built_at` | When this store's feed was last pre-built. A feed never built binds the epoch, so its first build is a full one | | `:now` | The time the feed renders | These make an incremental feed possible: ```sql systemmodstamp > :last_built_at ``` Each build records its own start time, so a record changed while a build was running is picked up by the next one. On a live request `:last_built_at` is the epoch, which makes the feed a full one. ## `select` Without a `select`, a feed returns whole records. Adding one switches to projection: the feed's records become exactly the columns listed, each addressed in the template by its alias. ```sql sfid AS id, name AS item_name, brand.s_c__display_name__c AS brand, STRING_AGG(cat.sfid, '|') AS group_ids, MIN(pbe.unitprice) AS price ``` The template shape does not change; only which fields exist: ```liquid {% for record in record_set.records %} {{ record.id }} - {{ record.item_name }} ({{ record.group_ids }}) {% endfor %} ``` You can project a base column, a to-one joined column, or an aggregate over a to-many join. A bare to-many column outside an aggregate is not projectable. ### Aggregates An aggregate reduces a to-many collection to one value per base record, such as joining every category id a product belongs to. `COUNT`, `SUM`, `MIN`, `MAX`, `AVG`, `STRING_AGG` and `JSON_AGG` are available, and `DISTINCT` is allowed inside any of them. - `STRING_AGG` requires a delimiter, and the others must not be given one. - `COUNT(*)` is not supported. Name the to-many alias, as in `COUNT(cat.sfid)`, so it is unambiguous which collection is being counted. - Aggregating a base or to-one column is rejected, because there is no collection to aggregate. - Aggregates over different collections compose without inflating each other's counts. - `JSON_AGG` projects a real array. In a JSON feed, printing it directly encodes it as a JSON array, and a record with no matches yields `[]` rather than nothing. ### Aliases A bare base field takes its own name as its alias. Anything else, a qualified column or any aggregate, requires an explicit `AS`, because a column with no alias cannot be addressed in the template. Aliases must be unique, and are lower-cased, so reference them in lower case whatever you wrote. :::warning A to-one join used only in `select` and never in `where` is still an `INNER` join, so base rows whose foreign key is empty are dropped from the feed. Declare it as `LEFT JOIN` to keep them, and the projected column comes through empty. :::