...
Attribute fields
System fields
Expressions with attribute or system fields
Attribute fields can be used in the QueryAPI only if you configure a name and data type for that attribute field. Attribute fields are accessed as a property by the configured label, e.g. px.Cost, cp.Country. Therefore it is not recommended to configure attribute labels with spaces, since it makes the code worse readable, however such column names are supported. Attribute fields cannot be accessed by their system field name, e.g. p.attribute3 will not work.
...
| Note |
|---|
Unity stores empty values of attribute fields in transactional database as an empty string. Due to the CAST above, QueryAPI performs a special CASE-WHEN check for empty string values in order to not cause crashes for empty values. E.g.:
This check cause index that has The check is omitted if the attribute field is marked with Required flag. The expression will be come become simple cast:
So if you can it is recommended to mark attribute fields with Required flag whenever possible. But it has to be ensured (e.g. in integration or by boundCall) that a value cannot be empty. |
...
Source table
Source table is spcified specified using source() method is telling which table will be queried. If you are familiar with SQL language, you can look at this method as “FROM” clause in SQL.
...
| Code Block | ||
|---|---|---|
| ||
def date = new Date()
def q = api.queryApi()
def t1 = q.tables().products()
def t2 = q.tables().productExtensionRows("Cost")
return q.source(t1, [t1.sku(), t1.ProductGroup])
.leftOuterJoin(t2, { cols -> [t2.Cost, t2.Currency] },
{ cols ->
q.exprs().and(
t2.sku().equal(cols.sku),
t2.ValidFrom.lessOrEqual(date),
t2.ValidTo.greaterOrEqual(date),
)
}
)
.stream { it.collect { it } } |
...
If you made several joins using leftOuterJoin or innerJoin you can apply additional filtering of the result using filter() method. The method takes a closure as the argument and should return an expression which if valuated to true, telss tells if the row should be present in the result.
...