Conditions

Conditions for where, having, filter, and on

Kronos condition DSL is used in where, having, filter, and on blocks. You can write Kotlin operators and Kronos condition helpers in the same expression.

val users = User()
    .select { [it.id, it.name, it.age] }
    .where { (it.age >= 18 && it.name like "Ada%") || it.id == 1 }
    .toList()

SELECT `id`, `name`, `age`
FROM `user`
WHERE (`user`.`age` >= :ageMin AND `user`.`name` LIKE :name) OR `user`.`id` = :id

Choose the filtering layer

where filters the current SQL layer's source fields. having filters grouped rows with source expressions and aggregates. Use filter after select { ... } when the predicate must read the selected result, including generated aliases.

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

val rows = Order()
    .select {
        [
            it.id,
            f.rowNumber()
                .over { orderBy(it.id.asc()) }
                .alias("rn")
        ]
    }
    .filter { it.rn > 1 }
    .toList()

SELECT `q`.`id`, `q`.`rn`
FROM (
    SELECT `id`,
           ROW_NUMBER() OVER (ORDER BY `id` ASC) AS rn
    FROM `order`
) AS `q`
WHERE `q`.`rn` > :rnMin

filter always creates this derived-query boundary. Its receiver is only the Selected result, so this example exposes id and rn, but no unselected Order fields. It preserves that result shape and is equivalent to calling select().where { ... } on the selected query explicitly.

Choose the where call

select() creates a query for the current table. Object property values enter the condition when you call where() or by { ... }.

val users = User(id = 1)
    .select()
    .toList()

SELECT `id`, `name`, `age`
FROM `user`

An empty where() uses the current KPojo's queryable non-null fields as equality conditions.

val users = User(id = 1)
    .select()
    .where()
    .toList()

SELECT `id`, `name`, `age`
FROM `user`
WHERE `user`.`id` = :id

Multiple queryable non-null fields participate in the same query-by-example condition.

val users = User(id = 1, name = "A", email = null)
    .select()
    .where()
    .toList()

SELECT `id`, `name`, `age`, `email`
FROM `user`
WHERE `user`.`id` = :id
  AND `user`.`name` = :name

Use == null or isNull in a lambda when the target value is SQL NULL.

val users = User()
    .select()
    .where { it.email == null }
    .toList()

SELECT `id`, `name`, `age`, `email`
FROM `user`
WHERE `user`.`email` IS NULL

Dynamic nullable values still use the no-value strategy. When email is null, the default SELECT behavior omits this condition.

val email: String? = null

val users = User()
    .select()
    .where { it.email == email }
    .toList()

A where { ... } call uses the lambda expression for that condition.

val users = User(id = 1)
    .select()
    .where { it.name == "A" }
    .toList()

SELECT `id`, `name`, `age`
FROM `user`
WHERE `user`.`name` = :name

Multiple where calls append conditions with AND.

val users = User(id = 1)
    .select()
    .where()
    .where { it.name == "A" }
    .toList()

SELECT `id`, `name`, `age`
FROM `user`
WHERE `user`.`id` = :id
  AND `user`.`name` = :name

Put complex OR, grouping, and function predicates in the same lambda.

val users = User(id = 1)
    .select()
    .where()
    .where { it.name == "A" || it.age > 18 }
    .toList()

SELECT `id`, `name`, `age`
FROM `user`
WHERE `user`.`id` = :id
  AND (`user`.`name` = :name OR `user`.`age` > :ageMin)

select().where() keeps the query unconditional when the object has no queryable non-null fields.

val users = User()
    .select()
    .where()
    .toList()

SELECT `id`, `name`, `age`
FROM `user`

update().where() and delete().where() use the same query-by-example rule to select target rows. Write operations with no queryable fields enter write-safety checks, and DataGuard can reject full-table writes consistently.

User(id = 1)
    .update()
    .set { it.name = "Kronos ORM" }
    .where()
    .execute()

User(id = 1)
    .delete()
    .where()
    .execute()

UPDATE `user`
SET `name` = :nameNew
WHERE `user`.`id` = :id

DELETE FROM `user`
WHERE `user`.`id` = :id

Logic-delete fields, cascade properties, ignored fields, and non-column properties are handled by their own strategies. Empty where() reads queryable database columns for query-by-example conditions.

Compare field values

Use Kotlin comparison operators for equality and range comparisons.

val users = User()
    .select()
    .where {
        it.name == "Ada" &&
            it.age >= 18 &&
            it.score < 100
    }
    .toList()

SELECT `id`, `name`, `age`, `score`
FROM `user`
WHERE `user`.`name` = :name
  AND `user`.`age` >= :ageMin
  AND `user`.`score` < :scoreMax

Use the no-argument condition properties when the value comes from the current KPojo instance.

val probe = User(name = "Ada", age = 18)

val users = probe
    .select()
    .where { it.name.eq && it.age.ge }
    .toList()

SELECT `id`, `name`, `age`
FROM `user`
WHERE `user`.`name` = :name
  AND `user`.`age` >= :ageMin

eq, neq, lt, gt, le, and ge read the property value from the object that starts the operation.

Combine conditions

Use &&, ||, !, and parentheses to control boolean composition.

val users = User()
    .select()
    .where {
        (it.status == 1 || it.status == 2) &&
            !(it.name like "test%")
    }
    .toList()

SELECT `id`, `name`, `status`
FROM `user`
WHERE (`user`.`status` = :status OR `user`.`status` = :status@1)
  AND `user`.`name` NOT LIKE :name

Kronos keeps the same expression order and parameterizes repeated field values with suffixes such as status@1.

Match a collection

Use in and !in with lists, arrays, or selectable subqueries.

val users = User()
    .select()
    .where { it.id in listOf(1, 2, 3) }
    .toList()

SELECT `id`, `name`, `age`
FROM `user`
WHERE `user`.`id` IN (:idList)

Arrays use the same IN predicate shape.

val users = User()
    .select()
    .where { it.id in arrayOf(1, 2, 3) }
    .toList()

Use a selectable query on the right side when the set comes from another table.

val users = User()
    .select()
    .where { user ->
        user.id in Order()
            .select { order -> order.userId }
            .where { order -> order.status == 1 }
    }
    .toList()

SELECT `id`, `name`, `age`
FROM `user`
WHERE `user`.`id` IN (
    SELECT `user_id` AS `userId`
    FROM `order`
    WHERE `order`.`status` = :status
)

More subquery predicates, row-value tuples, and quantified comparisons are covered in 📚Subqueries.

Build conditions from an Iterable

Use Kotlin Iterable.any, Iterable.all, and Iterable.none predicate calls inside a condition block. The lambda builds one condition for each iterable element.

val keywords = listOf("Ada", "Grace")
val minimumAges = listOf(18, 21)
val excludedFragments = listOf("test", "archive")

val users = User()
    .select()
    .where {
        keywords.any { keyword -> it.name.contains(keyword) } &&
            minimumAges.all { minimumAge -> it.age >= minimumAge } &&
            excludedFragments.none { fragment -> it.name.contains(fragment) }
    }
    .toList()

For a non-empty iterable, Kronos combines the generated conditions in iterable order:

With !, !values.any { p(it) } combines NOT p(...) with AND; !values.all { p(it) } combines NOT p(...) with OR; !values.none { p(it) } combines p(...) with OR.

An empty iterable, or an iterable whose generated child conditions all have no value, produces a FALSE condition for any, all, and none.

! retains the FALSE condition. This keeps the predicate present for select, update, and delete operations.

Match a range

Use between and notBetween with Kotlin ranges.

val users = User()
    .select()
    .where { it.age between 18..40 }
    .toList()

SELECT `id`, `name`, `age`
FROM `user`
WHERE `user`.`age` BETWEEN 18 AND 40

val users = User()
    .select()
    .where { it.age notBetween 1..17 }
    .toList()

SELECT `id`, `name`, `age`
FROM `user`
WHERE `user`.`age` NOT BETWEEN 1 AND 17

Match strings

Use like and notLike when the pattern is already prepared.

val users = User()
    .select()
    .where { it.name like "Ada%" }
    .toList()

SELECT `id`, `name`, `age`
FROM `user`
WHERE `user`.`name` LIKE :name

Use startsWith, endsWith, and contains to let Kronos build the % pattern.

val users = User()
    .select()
    .where {
        it.name.startsWith("A") ||
            it.name.endsWith("son") ||
            it.name.contains("ron")
    }
    .toList()

SELECT `id`, `name`, `age`
FROM `user`
WHERE `user`.`name` LIKE :name
   OR `user`.`name` LIKE :name@1
   OR `user`.`name` LIKE :name@2

Use regexp and notRegexp for database regular-expression predicates.

val users = User()
    .select()
    .where { it.name regexp "^A.*" }
    .toList()

SELECT `id`, `name`, `age`
FROM `user`
WHERE `user`.`name` REGEXP :namePattern

Kronos renders the same predicate with each database's regular-expression syntax.

SQLite applications can register a regular-expression function through 📚Custom Functions. SQL Server applications can expose their chosen regular-expression implementation through the same extension point.

Match normalized text

f.lower(x) and f.upper(x) return nullable String? expressions. Use them with equality, contains, like, startsWith, endsWith, and membership conditions.

import com.kotlinorm.functions.bundled.exts.StringFunctions.lower
import com.kotlinorm.functions.bundled.exts.StringFunctions.upper

val normalizedName = "ADA".lowercase()
val allowedNames = listOf("ada", "grace")

val users = User()
    .select()
    .where {
        f.lower(it.userName) == normalizedName ||
            f.lower(it.userName).contains(normalizedName) ||
            normalizedName in f.lower(it.userName) ||
            f.lower(it.userName) like "ada%" ||
            f.lower(it.userName).startsWith("a") ||
            f.lower(it.userName).endsWith("a") ||
            f.lower(it.userName) in allowedNames
    }
    .toList()

f.upper(...) accepts the same condition helpers with uppercase values. in supports text containment, collection membership, and single-column subqueries.

Use native Kotlin case calls

Kronos maps no-argument lowercase() and uppercase() calls on source String fields to SQL LOWER and UPPER inside a condition. The generated expressions use the same condition operators.

val normalizedName = "Ada".lowercase()

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

Other native String receiver calls use the same source-field rule in a condition:

val users = User()
    .select()
    .where {
        it.userName?.length == 3 ||
            it.userName?.take(3) == "VIP" ||
            it.userName?.replace("-", "") == "Ada"
    }
    .toList()

Use substring(start, end) with Kotlin's zero-based, end-exclusive indices. The full list of length, count(), replace, substring, subSequence, take, and takeLast calls is in 📚Built-in Functions.

Captured values such as normalizedName are evaluated by Kotlin and bound as condition parameters. 📚Built-in Functions includes function and dialect examples.

Read values from the current object

No-argument match helpers read the value from the object that starts the operation.

val probe = User(name = "Ada")

val users = probe
    .select()
    .where { it.name.startsWith }
    .toList()

SELECT `id`, `name`, `age`
FROM `user`
WHERE `user`.`name` LIKE :name

The bound value is Ada%.

Test null values

Use == null / != null or isNull / notNull for SQL null checks.

val users = User()
    .select()
    .where { it.deletedAt == null || it.email != null }
    .toList()

SELECT `id`, `name`, `email`, `deleted_at` AS `deletedAt`
FROM `user`
WHERE `user`.`deleted_at` IS NULL OR `user`.`email` IS NOT NULL

Compare fields and object values

Use a field on the right side to compare two columns.

val users = User()
    .select()
    .where { it.age == it.otherAge }
    .toList()

SELECT `id`, `age`, `other_age` AS `otherAge`
FROM `user`
WHERE `user`.`age` = `user`.`other_age`

Relationship field chains should use a safe call. Kronos reads the related field metadata and compares columns; it does not read the runtime director object.

val movies = Movie()
    .select()
    .where { it.directorId == it.director?.id }
    .toList()

// it.director!!.id is also supported.

SELECT `id`, `director_id` AS `directorId`, `title`
FROM `movie`
WHERE `movie`.`director_id` = `director`.`id`

Use .value as the final access when you need the actual Kotlin value of a KPojo property from another object.

Ordinary variables and current source fields use their direct form.

val probe = User(otherAge = 40)

val users = User()
    .select()
    .where { it.age == probe.otherAge.value }
    .toList()

SELECT `id`, `age`
FROM `user`
WHERE `user`.`age` = :age

Use functions and arithmetic

Built-in functions and arithmetic expressions can appear inside conditions.

val users = User()
    .select { [it.id, it.name] }
    .where {
        it.score + 10 > it.score - 10 &&
            f.length(it.name) > 5
    }
    .toList()

SELECT `id`, `name`
FROM `user`
WHERE (`score` + 10) > (`score` - 10)
  AND LENGTH(`name`) > :lengthMin

Function details are covered in 📚Built-in Functions.

Window functions are selected expressions. Use them in select { ... }, then use filter to apply a predicate to their aliases through a derived query; see 📚Subqueries.

Expand object equality

Use it.eq to expand all non-empty fields of the current object into equality conditions.

val probe = User(id = 1, name = "Ada", age = 36)

val users = probe
    .select()
    .where { it.eq }
    .toList()

SELECT `id`, `name`, `age`
FROM `user`
WHERE `user`.`id` = :id
  AND `user`.`name` = :name
  AND `user`.`age` = :age

Use - before .eq to exclude fields from the expansion.

val probe = User(id = 1, name = "Ada", age = 36)

val users = probe
    .select()
    .where { (it - it.age).eq }
    .toList()

SELECT `id`, `name`, `age`
FROM `user`
WHERE `user`.`id` = :id
  AND `user`.`name` = :name

Add raw SQL predicates

Use .asSql() when a predicate must be written as SQL text.

val users = User()
    .select()
    .where { "`name` = :name AND `age` > :age".asSql() }
    .patch("name" to "Ada", "age" to 18)
    .toList()

SELECT `id`, `name`, `age`
FROM `user`
WHERE `name` = :name AND `age` > :age

patch supplies named parameters that are not read from the current KPojo object.

Build dynamic predicates

Use .takeIf(...) to keep a predicate when a Kotlin condition is true, or .takeUnless(...) to keep it when the condition is false.

val minAge: Int? = null
val includeInactive = false

val users = User()
    .select()
    .where {
        (it.age >= minAge).takeIf(minAge != null) &&
            (it.status == 0).takeUnless(includeInactive)
    }
    .toList()

SELECT `id`, `name`, `age`
FROM `user`
WHERE `status` = :status

Ordinary Kotlin if and when expressions can select complete SQL predicate branches:

data class UserFilter(val id: Int? = null, val name: String? = null)

val filter = UserFilter(id = 7)
val users = User()
    .select()
    .where {
        when {
            filter.id != null -> it.id == filter.id
            filter.name != null -> it.name == filter.name
            else -> it.active == true
        }
    }
    .toList()

The Boolean arguments of takeIf/takeUnless and the conditions of if/when are ordinary Kotlin control flow.

Properties of ordinary Kotlin objects are runtime values in SQL comparisons, so it.id == filter.id uses the direct form. This includes regular classes, data classes, objects, companion or @JvmStatic properties, and top-level properties. Current source fields also participate directly in condition expressions.

For a captured KPojo outside the current query source, end the property chain with .value to read its actual Kotlin value, for example it.id == probe.id.value.

Default no-value handling for null values and empty collections is described in 📚No Value Strategy.

Use conditions in having

having uses the same condition DSL as where.

val rows: List<Map<String, Any?>> = Order()
    .select { [it.userId, f.count(1).alias("orderCount")] }
    .groupBy { it.userId }
    .having { f.count(1) > 3 }
    .toMapList()

SELECT `user_id` AS `userId`, COUNT(1) AS orderCount
FROM `order`
GROUP BY `user_id`
HAVING COUNT(1) > :countMin

Use conditions in on

leftJoin, rightJoin, innerJoin, and fullJoin use the same condition DSL for join predicates. crossJoin() is conditionless.

val rows = User().join(Order()) { user, order ->
    leftJoin { user.id == order.userId && order.status == 1 }
        .select { [user.id, user.name, order.status] }
}.toList()

SELECT `user`.`id`, `user`.`name`, `order`.`status`
FROM `user`
LEFT JOIN `order`
ON `user`.`id` = `order`.`user_id` AND `order`.`status` = :status

Join syntax is covered in 📚Select Join Tables.