Functions

This article will guide you on how to use Kronos' built-in functions and custom functions.

Use Built-In Functions

Kronos exposes built-in functions through the f receiver inside query DSL blocks. Use them in select, where, having, orderBy, and join on conditions.

val rows = User()
    .select {
        [
            it.id,
            f.length(it.name).alias("nameLength"),
            f.abs(it.score).alias("absoluteScore")
        ]
    }
    .where { f.length(it.name) > 3 && f.abs(it.score) > 10 }
    .orderBy { it.nameLength.desc() }
    .toList()

SELECT `id`,
       LENGTH(`name`) AS `nameLength`,
       ABS(`score`) AS `absoluteScore`
FROM `user`
WHERE LENGTH(`name`) > :nameLength
  AND ABS(`score`) > :absoluteScore
ORDER BY `nameLength` DESC

Function expressions in select { ... } should usually use .alias("name"). The alias becomes the toMapList() map key, the generated projection property name, and a field that same-layer orderBy can read. See 📚Projection for result shapes.

val first = rows.first()
val nameLength: Int? = first.nameLength
val absoluteScore: Int? = first.absoluteScore

Function SQL is rendered by the active database dialect. The complete built-in dialect list is covered in 📚Dialect Support.

Aggregate Functions

Aggregate functions can be used with groupBy and having. Alias aggregate expressions when they are returned as selected columns.

val rows = Order()
    .select {
        [
            it.userId,
            f.count(it.id).alias("orderCount"),
            f.sum(it.amount).alias("totalAmount")
        ]
    }
    .groupBy { it.userId }
    .having { f.count(it.id) > 1 }
    .toList()

SELECT `user_id` AS `userId`,
       COUNT(`id`) AS `orderCount`,
       SUM(`amount`) AS `totalAmount`
FROM `order`
GROUP BY `user_id`
HAVING COUNT(`id`) > :orderCount

val first = rows.first()
val userId: Int? = first.userId
val orderCount: Int? = first.orderCount
val totalAmount: java.math.BigDecimal? = first.totalAmount

Use groupConcat for grouped display values when their sequence is not part of the result contract. Do not rely on a cross-database result order; Oracle and DM8 order the supported simple form by the aggregated expression in ascending order.

Math Functions And Operators

Use Kotlin operators for addition, subtraction, multiplication, division, and modulo. Use f for the other math functions.

val rows = User()
    .select {
        [
            it.id,
            (it.score + 10).alias("scorePlus"),
            (it.score % 2).alias("scoreMod"),
            f.ceil(it.score).alias("scoreCeil"),
            f.trunc(it.score, 2).alias("scoreTrunc")
        ]
    }
    .toList()

SELECT `id`,
       (`score` + 10) AS `scorePlus`,
       MOD(`score`, 2) AS `scoreMod`,
       CEIL(`score`) AS `scoreCeil`,
       TRUNCATE(`score`, 2) AS `scoreTrunc`
FROM `user`

f.log(value, base) keeps the value-first Kotlin call shape across the supported dialects. f.bin(x) is available for MySQL expressions.

String Functions

String functions can be selected as result fields or used in conditions.

val users = User()
    .select {
        [
            it.id,
            f.upper(it.name).alias("upperName"),
            f.concat(it.name, "-", it.status).alias("displayName")
        ]
    }
    .where { f.length(it.name) > 3 }
    .toList()

SELECT `id`,
       UPPER(`name`) AS `upperName`,
       CONCAT(`name`, '-', `status`) AS `displayName`
FROM `user`
WHERE LENGTH(`name`) > :nameLength

f.join(separator, x, y, ...) joins the provided values and skips null arguments. H2 applications that need reverse text can expose a custom database function; the built-in H2 dialect reports f.reverse as unavailable.

Function Availability

Native Kotlin case calls in conditions

For a source String field in a condition, the no-argument Kotlin calls lowercase() and uppercase() use the same SQL LOWER and UPPER expressions as f.lower(...) and f.upper(...).

val name = "Ada"

val users = User()
    .select()
    .where { it.userName?.lowercase() == name.lowercase() }
    .toList()

SELECT `id`, `user_name` AS `userName`
FROM `user`
WHERE LOWER(`user_name`) = :userName

contains keeps its escaped %value% matching behavior, while like accepts the pattern you provide.

val name = "Ada"

val users = User()
    .select()
    .where {
        it.userName?.lowercase().contains(name.lowercase()) ||
            it.userName?.uppercase() like "ADA%"
    }
    .toList()

SELECT `id`, `user_name` AS `userName`
FROM `user`
WHERE LOWER(`user_name`) LIKE :userName ESCAPE '\'
   OR UPPER(`user_name`) LIKE :userName@1

The captured name.lowercase() value is evaluated by Kotlin and bound as a parameter.

Native Kotlin String receiver calls

Non-null source String fields can also use these Kotlin calls directly in select projections, conditions, and KSelectable.insert mappings:

count() is the length of the current string, not aggregate f.count(...). substring keeps Kotlin indexing: start is zero-based and end is exclusive.

val rows = User()
    .select {
        [
            it.name.length.alias("nameLength"),
            it.name.replace("-", " ").alias("displayName"),
            it.name.substring(0, 8).alias("prefix"),
            it.name.takeLast(4).alias("suffix")
        ]
    }
    .where { it.name.take(3) == "VIP" }
    .toList()

Use the normal two-argument replace form, or ignoreCase = false. The case-insensitive ignoreCase = true form is not a native SQL receiver call. Captured arguments such as old and new are evaluated by Kotlin and bound as parameters.

Source fields and normal variables use their direct form. When a KPojo property's actual value is required, end that property chain with .value, for example probe.userName.value.

See 📚Conditions for condition matching rules.

Use Window Functions

Kronos currently exposes f.rowNumber() for window queries. Import com.kotlinorm.functions.bundled.exts.WindowFunctions.rowNumber before using it and include an orderBy(...) clause in over { ... }.

import com.kotlinorm.functions.bundled.exts.WindowFunctions.rowNumber

val ranked = Order()
    .select {
        [
            it.id,
            it.userId,
            it.status,
            f.rowNumber()
                .over {
                    partitionBy(it.userId)
                    orderBy(it.status.desc())
                }
                .alias("rn")
        ]
    }

val rows = ranked
    .orderBy { it.rn.asc() }
    .toList()

SELECT `id`,
       `user_id` AS `userId`,
       `status`,
       ROW_NUMBER() OVER (PARTITION BY `user_id` ORDER BY `status` DESC) AS rn
FROM `order`
ORDER BY `rn` ASC

Window aliases can be sorted in the same query. When a predicate needs a window alias, select it first and use filter to apply the predicate through a derived query; see 📚Subqueries.

val first = rows.first()
val rowNumber: Int? = first.rn

val firstPerUser = ranked
    .filter { it.rn == 1 }
    .toList()

SELECT `q`.`id`, `q`.`userId`, `q`.`status`, `q`.`rn`
FROM (
    SELECT `id`,
           `user_id` AS `userId`,
           `status`,
           ROW_NUMBER() OVER (PARTITION BY `user_id` ORDER BY `status` DESC) AS rn
    FROM `order`
) AS `q`
WHERE `q`.`rn` = :rn

PostgreSQL Array Helpers

Use f.any(...) and f.all(...) for PostgreSQL array comparisons. Import the helpers before using them.

These f receiver functions render PostgreSQL array comparison expressions.

import com.kotlinorm.functions.bundled.exts.PostgresFunctions.all
import com.kotlinorm.functions.bundled.exts.PostgresFunctions.any

val acceptedIds = intArrayOf(1, 2, 3)
val blockedIds = intArrayOf(8, 9)

val rows = User()
    .select()
    .where {
        it.id == f.any(acceptedIds) &&
            it.id != f.all(blockedIds)
    }
    .toList()

The condition renders PostgreSQL ANY (...) and ALL (...) array predicates.

Custom Functions

Use custom function extensions for project-specific or dialect-specific functions. See 📚Custom Functions.