Expressions

Expressions are used within attributes and other constructs to define conditions, default values, etc.

Expressions support logical and comparison operations:

  • Operators: ==, !=, >, <, >=, <=, in, !, &&, ||, =, +, -, *, /
  • Aggregate functions: SUM, COUNT, AVG, MIN, MAX, MEDIAN and conditional variants (see list below)
  • Scalar functions: IF, date extraction, date arithmetic, and DIFF (see list below)
  • Values: Literals (true, false, null, numbers, strings), identifiers, arrays.
  • Parentheses: Used for grouping, e.g., (a == b) && (c > d)

All operators, aggregate functions, and scalar functions can be used in @where, @set, @permission, and @computed expressions.

Arithmetic Operators

Arithmetic operators (+, -, *, /) can be used for calculations in any expression context.

Using in @set

update calculateTotal(id) {
    @set(order.total = order.subtotal + order.shipping)
}
 
update applyDiscount(id) {
    @set(order.finalPrice = order.total * 0.9)
}

Using in @where

list listHighValueProducts() {
    @where(product.price * product.quantity > 1000)
}
 
list listDiscountedProducts() {
    @where(product.basePrice - product.discount < 50)
}

Using in @permission

list listBudgetsWithRemainingBalance() {
    @permission(expression: budget.limit - budget.spent > 100)
}

Using in @computed

model Order {
  fields {
    subtotal Decimal
    shipping Decimal
    total Decimal @computed(order.subtotal + order.shipping)
    vat Decimal @computed(order.total * 0.2)
  }
}

Aggregate Functions

Aggregate functions perform calculations across a collection of related records. They are commonly used with one-to-many relationships and can be used in @computed, @set, @where, and @permission expressions.

SUM

Adds up all numeric values in a collection.

Syntax: SUM(items)

Example usages:

// In @computed
totalValue Decimal @computed(SUM(customer.orders.total))
 
// In @set
update calculateTotalOrderValue(id) {
    @set(customer.totalOrderValue = SUM(customer.orders.total))
}
 
// In @where
list listHighValueCustomers() {
    @where(SUM(customer.orders.total) > 500)
}

COUNT

Counts the number of items in a collection.

Syntax: COUNT(items)

Example usages:

// In @computed
orderCount Number @computed(COUNT(customer.orders.id))
 
// In @set
update calculateOrderCount(id) {
    @set(customer.orderCount = COUNT(customer.orders.id))
}
 
// In @where
list listCustomersWithMultipleOrders() {
    @where(COUNT(customer.orders.id) >= 2)
}
 
// In @permission
list listProjectsWithTasks() {
    @permission(expression: COUNT(project.tasks.id) > 0)
}

AVG

Computes the arithmetic average of numeric values in a collection.

Syntax: AVG(items)

Example usages:

// In @computed
averageOrderValue Decimal @computed(AVG(customer.orders.total))
 
// In @set
update calculateAverageOrderValue(id) {
    @set(customer.averageOrderValue = AVG(customer.orders.total))
}
 
// In @where
list listCustomersWithHighAverageOrders() {
    @where(AVG(customer.orders.total) > 100)
}

MEDIAN

Computes the arithmetic median of the numeric values in a collection.

Syntax: MEDIAN(items)

Example usages:

// In @computed
medianOrderValue Decimal @computed(MEDIAN(customer.orders.total))
 
// In @where
list listCustomersWithHighMedianOrders() {
    @where(MEDIAN(customer.orders.total) > 75)
}

MIN

Finds the smallest numeric value in a collection.

Syntax: MIN(items)

Example usages:

// In @computed
minOrderValue Decimal @computed(MIN(customer.orders.total))
 
// In @set
update calculateMinOrderValue(id) {
    @set(customer.minOrderValue = MIN(customer.orders.total))
}

MAX

Finds the largest numeric value in a collection.

Syntax: MAX(items)

Example usages:

// In @computed
maxOrderValue Decimal @computed(MAX(customer.orders.total))
 
// In @set
update calculateMaxOrderValue(id) {
    @set(customer.maxOrderValue = MAX(customer.orders.total))
}

SUMIF

Adds up numeric values that satisfy a given condition.

Syntax: SUMIF(items, condition)

Example usages:

// In @computed
activeOrderTotal Decimal @computed(SUMIF(customer.orders.total, customer.orders.isActive))
 
// In @set
update calculateActiveOrderTotal(id) {
    @set(customer.activeOrderTotal = SUMIF(customer.orders.total, customer.orders.isActive))
}
 
// In @where
list listCustomersWithHighActiveOrderValue() {
    @where(SUMIF(customer.orders.total, customer.orders.isActive) > 200)
}

COUNTIF

Counts items that satisfy a given condition.

Syntax: COUNTIF(items, condition)

Example usages:

// In @computed
activeOrderCount Number @computed(COUNTIF(customer.orders.id, customer.orders.isActive))
 
// In @set
update calculateActiveOrderCount(id) {
    @set(customer.activeOrderCount = COUNTIF(customer.orders.id, customer.orders.isActive))
}
 
// In @where
list listCustomersWithActiveOrders() {
    @where(COUNTIF(customer.orders.id, customer.orders.isActive) >= 1)
}

AVGIF

Computes the average of numeric values that satisfy a given condition.

Syntax: AVGIF(items, condition)

Example usages:

// In @computed
activeOrderAvg Decimal @computed(AVGIF(customer.orders.total, customer.orders.isActive))
 
// In @set
update calculateActiveOrderAvg(id) {
    @set(customer.activeOrderAvg = AVGIF(customer.orders.total, customer.orders.isActive))
}

MEDIANIF

Finds the median of numeric values that satisfy a given condition.

Syntax: MEDIANIF(items, condition)

Example usages:

// In @computed
medianActiveOrder Decimal @computed(MEDIANIF(customer.orders.total, customer.orders.isActive))
 
// In @where
list listCustomersWithHighActiveMedian() {
    @where(MEDIANIF(customer.orders.total, customer.orders.isActive) > 100)
}

MINIF

Finds the smallest numeric value that satisfies a given condition.

Syntax: MINIF(items, condition)

Example usages:

// In @computed
minActiveOrder Decimal @computed(MINIF(customer.orders.total, customer.orders.isActive))
 
// In @set
update calculateActiveOrderMin(id) {
    @set(customer.activeOrderMin = MINIF(customer.orders.total, customer.orders.isActive))
}

MAXIF

Finds the largest numeric value that satisfies a given condition.

Syntax: MAXIF(items, condition)

Example usages:

// In @computed
maxActiveOrder Decimal @computed(MAXIF(customer.orders.total, customer.orders.isActive))
 
// In @set
update calculateActiveOrderMax(id) {
    @set(customer.activeOrderMax = MAXIF(customer.orders.total, customer.orders.isActive))
}

Scalar Functions

Scalar functions operate on individual values and return a single result. They can be used in @computed, @set, @where, and @permission expressions.

IF

Returns one value if a condition is true, and another if false. Useful for conditional logic in expressions.

Syntax: IF(condition, thenValue, elseValue)

Example usages:

// In @computed - conditional text based on boolean field
importanceLabel Text @computed(IF(appointment.isImportant, "High Priority", "Normal"))
 
// In @computed - conditional pricing
finalPrice Decimal @computed(IF(product.isActive, product.price * 0.9, product.price))
 
// In @set - apply discount based on condition
update applyActiveDiscount(id) {
    @set(product.discountedPrice = IF(product.isActive, product.price * 0.9, product.price))
}
 
// In @set - set status text
update setStatus(id) {
    @set(product.status = IF(product.isActive, "Available", "Discontinued"))
}
 
// Nested IF for multiple conditions
update setTier(id) {
    @set(product.tier = IF(product.isPremium, "Premium", IF(product.isBulk, "Bulk", "Standard")))
}
 
// In @where
list listActiveOrExpensiveProducts(minPrice: Decimal) {
    @where(IF(product.isActive, product.price, product.price * 2) > minPrice)
}

Date Extraction Functions

These functions extract specific components from Date or Timestamp fields.

YEAR

Extracts the year from a date or timestamp.

Syntax: YEAR(dateOrTimestamp)

Example usages:

// In @computed
scheduledYear Number @computed(YEAR(appointment.scheduledDate))
 
// In @set
update extractYear(id) {
    @set(event.extractedYear = YEAR(event.eventDate))
}
 
// In @where
list listEventsInYear(year: Number) {
    @where(YEAR(event.eventDate) == year)
}

MONTH

Extracts the month (1-12) from a date or timestamp.

Syntax: MONTH(dateOrTimestamp)

Example usages:

// In @computed
scheduledMonth Number @computed(MONTH(appointment.scheduledDate))
 
// In @where
list listEventsInMonth(month: Number) {
    @where(MONTH(event.eventDate) == month)
}

DAY

Extracts the day of the month (1-31) from a date or timestamp.

Syntax: DAY(dateOrTimestamp)

Example usages:

// In @computed
scheduledDay Number @computed(DAY(appointment.scheduledDate))
 
// In @where
list listEventsOnDay(day: Number) {
    @where(DAY(event.eventDate) == day)
}

HOUR

Extracts the hour (0-23) from a timestamp.

Syntax: HOUR(timestamp)

Example usages:

// In @computed
scheduledHour Number @computed(HOUR(appointment.scheduledTimestamp))
 
// In @where
list listEventsAtHour(hour: Number) {
    @where(HOUR(event.eventTimestamp) == hour)
}

MINUTE

Extracts the minute (0-59) from a timestamp.

Syntax: MINUTE(timestamp)

Example usage:

// In @computed
scheduledMinute Number @computed(MINUTE(appointment.scheduledTimestamp))

SECOND

Extracts the second (0-59) from a timestamp.

Syntax: SECOND(timestamp)

Example usage:

// In @set
update extractSecond(id) {
    @set(event.extractedSecond = SECOND(event.eventTimestamp))
}

Date Arithmetic Functions

These functions add or subtract time periods from Date or Timestamp fields.

ADDDAYS

Adds or subtracts days from a date or timestamp.

Syntax: ADDDAYS(dateOrTimestamp, numberOfDays)

Example usages:

// In @set - add days
update addDaysToDate(id) {
    @set(event.newDate = ADDDAYS(event.eventDate, 10))
}
 
// In @set - subtract days (use negative number)
update subtractDaysFromDate(id) {
    @set(event.newDate = ADDDAYS(event.eventDate, -7))
}
 
// Combined with IF
update adjustDeadlineByPriority(id) {
    @set(task.adjustedDeadline = IF(task.isHighPriority, ADDDAYS(task.deadline, -3), ADDDAYS(task.deadline, 7)))
}

ADDMONTHS

Adds or subtracts months from a date or timestamp.

Syntax: ADDMONTHS(dateOrTimestamp, numberOfMonths)

Example usages:

// In @set - add months
update addMonthsToDate(id) {
    @set(event.newDate = ADDMONTHS(event.eventDate, 3))
}
 
// In @set - subtract months
update subtractMonthsFromDate(id) {
    @set(event.newDate = ADDMONTHS(event.eventDate, -1))
}

ADDYEARS

Adds or subtracts years from a date or timestamp.

Syntax: ADDYEARS(dateOrTimestamp, numberOfYears)

Example usages:

// In @set - add years
update addYearsToDate(id) {
    @set(event.newDate = ADDYEARS(event.eventDate, 1))
}
 
// In @set - subtract years
update subtractYearsFromDate(id) {
    @set(event.newDate = ADDYEARS(event.eventDate, -5))
}

DIFF

Calculates the difference in days between two dates or timestamps. Returns a positive number if the first date is after the second, or negative if before.

Syntax: DIFF(dateOrTimestamp1, dateOrTimestamp2)

Example usages:

// In @computed
durationDays Number @computed(DIFF(appointment.endDate, appointment.scheduledDate))
 
// In @set
update calculateDaysDiff(id) {
    @set(event.daysDifference = DIFF(event.dueDate, event.eventDate))
}
 
// In @where - find tasks due within a certain number of days
list listTasksDueSoon(maxDays: Number) {
    @where(DIFF(task.deadline, task.startedAt) <= maxDays)
}
 
// In @where - find overdue events
list listOverdueEvents() {
    @where(DIFF(event.dueDate, event.eventDate) < 0)
}
Schema ReferenceCLI