Sorting, Pagination, and Aggregation

This chapter describes orderBy, limit, page, withTotal, aggregate functions, groupBy, and having.

Sorting, pagination, and aggregation

Kronos keeps ordering, paging, grouping, aggregate functions, and aggregate filters on the query builder. These APIs work after normal select() calls, generated projections, and join queries.

Projection aliases and generated result shapes are covered in 📚Projection. Function details are covered in 📚Functions.

Sort with orderBy

Use orderBy { ... } with asc() or desc(). Multiple sort items are written with [].

val users = User()
    .select { [it.id, it.name, it.age] }
    .where { it.age >= 18 }
    .orderBy { [it.age.desc(), it.name.asc()] }
    .toList()

SELECT `id`, `name`, `age`
FROM `user`
WHERE `user`.`age` >= :ageMin
ORDER BY `age` DESC, `name` ASC

When no direction helper is used, the field sorts ascending.

User()
    .select()
    .orderBy { it.id }
    .toList()

Sort by selected aliases

orderBy { ... } can read the current query context, including selected aliases.

val rows = User()
    .select { [it.id, f.length(it.name).alias("nameLength")] }
    .orderBy { it.nameLength.desc() }
    .toList()

SELECT `id`, LENGTH(`name`) AS `nameLength`
FROM `user`
ORDER BY `nameLength` DESC

Use filter when a predicate should read the selected alias through a derived query; see 📚Subqueries.

Sort by a window alias

Window functions are selected expressions. Alias the window result, then sort by that alias in the same query.

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

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

Filtering a window alias uses filter and a derived query; see 📚Subqueries. The function entry point is covered in 📚Functions.

Limit rows

Use limit(count) when only the first rows are needed.

val users = User()
    .select()
    .orderBy { it.id.asc() }
    .limit(10)
    .toList()

SELECT `id`, `name`, `age`
FROM `user`
ORDER BY `id` ASC
LIMIT 10

first() and toMap() also apply a single-row limit for single-row results.

Page rows

Use page(pageIndex, pageSize) for an offset page. Page indexes start at 1. The returned OffsetPageQuery executes one SELECT and toList() returns only the records.

val query = User()
    .select()
    .where { it.age >= 18 }
    .orderBy { it.id.asc() }

val rows: List<User> = query
    .page(pageIndex = 2, pageSize = 20)
    .toList()

SELECT `id`, `name`, `age`
FROM `user`
WHERE `user`.`age` >= :ageMin
ORDER BY `id` ASC
LIMIT 20 OFFSET 20

Call withTotal() on the offset page when the result also needs the total count. toList() returns a named PageResult with total, records, totalPages, pageIndex, and pageSize.

val page: PageResult<User> = query
    .page(pageIndex = 2, pageSize = 20)
    .withTotal()
    .toList()

val total = page.total
val records = page.records
val totalPages = page.totalPages

SELECT COUNT(*)
FROM (
    SELECT 1
    FROM `user`
    WHERE `user`.`age` >= :ageMin
) AS total_count

An offset page remains a finite KSelectable, so it can become a derived source with its inner LIMIT/OFFSET intact. A total-page stage is execution-only and cannot be selected, joined, paged, or cursored again.

val finiteSource = query.page(pageIndex = 2, pageSize = 20)

val outerRows = finiteSource
    .select { [it.id, it.name] }
    .where { it.name != null }
    .toList()

Cursor rows

Use cursor(pageSize, after) for cursor pagination. Pass the returned nextCursor as after on the next call. toList() returns a named CursorResult with hasNext, nextCursor, and records.

val query = User()
    .select { [it.id, it.name, it.age] }
    .where { it.age >= 18 }
    .orderBy { it.id.asc() }

val firstPage: CursorResult<User> = query
    .cursor(pageSize = 20)
    .toList<User>()

val nextPage: CursorResult<User> = query
    .cursor(pageSize = 20, after = firstPage.nextCursor)
    .toList<User>()

SELECT `id`, `name`, `age`
FROM `user`
WHERE `user`.`age` >= :ageMin AND `id` > :cursor_id
ORDER BY `id` ASC
LIMIT 21

Cursor ordering must use selected fields. If the requested order is not unique, the selected output must retain a complete primary or unique key so Kronos can append a stable tie-breaker. A cursor page is execution-only: it cannot call page, withTotal, or become a derived source. Creating page and cursor views does not mutate the reusable base query.

Select aggregate values

Use aggregate functions in select { ... }. Add aliases when the result has more than one value or when the aggregate should be returned as a named property.

val rows = User()
    .select {
        [
            f.count(1).alias("count"),
            f.sum(it.score).alias("scoreSum"),
            f.avg(it.score).alias("scoreAvg"),
            f.min(it.score).alias("scoreMin"),
            f.max(it.score).alias("scoreMax")
        ]
    }
    .toList()

SELECT COUNT(1) AS count,
       SUM(`score`) AS `scoreSum`,
       AVG(`score`) AS `scoreAvg`,
       MIN(`score`) AS `scoreMin`,
       MAX(`score`) AS `scoreMax`
FROM `user`

data class UserScoreStats(
    val count: Number?,
    val scoreSum: Number?,
    val scoreAvg: Number?,
    val scoreMin: Number?,
    val scoreMax: Number?
)

Group and filter aggregate rows

Use groupBy { ... } for group keys and having { ... } for aggregate filters. having uses the same condition DSL as where.

val rows = User()
    .select {
        [
            it.gender,
            f.count(1).alias("count"),
            f.avg(it.score).alias("scoreAvg")
        ]
    }
    .groupBy { it.gender }
    .having { f.count(1) > 5 && f.avg(it.score) > 50 }
    .orderBy { it.scoreAvg.desc() }
    .toList()

SELECT `gender`,
       COUNT(1) AS count,
       AVG(`score`) AS `scoreAvg`
FROM `user`
GROUP BY `gender`
HAVING COUNT(1) > :countMin AND AVG(`score`) > :avgMin
ORDER BY `scoreAvg` DESC

The returned rows contain the group key and aggregate aliases.

listOf(
    mapOf("gender" to 1, "count" to 12, "scoreAvg" to 86.5),
    mapOf("gender" to 0, "count" to 9, "scoreAvg" to 79.0)
)

Use filter when the next predicate should read an aggregate alias instead of repeating the aggregate expression in the same-layer having clause.

val scoredGroups = User()
    .select {
        [
            it.gender,
            f.count(1).alias("count"),
            f.avg(it.score).alias("scoreAvg")
        ]
    }
    .groupBy { it.gender }
    .having { f.count(1) > 5 }

val rows = scoredGroups
    .filter { it.scoreAvg > 80 }
    .toList()

SELECT `q`.`gender`, `q`.`count`, `q`.`scoreAvg`
FROM (
    SELECT `gender`,
           COUNT(1) AS count,
           AVG(`score`) AS `scoreAvg`
    FROM `user`
    GROUP BY `gender`
    HAVING COUNT(1) > :countMin
) AS `q`
WHERE `q`.`scoreAvg` > :scoreAvgMin

having filters groups in the inner aggregation layer. filter always establishes the outer derived query, and its receiver exposes only scoredGroups's Selected fields: gender, count, and scoreAvg. Unlike the query-by-example where(), filter has no no-argument form; pass a predicate lambda.

Use [] for multiple group keys.

User()
    .select { [it.gender, it.age, f.count(1).alias("count")] }
    .groupBy { [it.gender, it.age] }
    .toMapList()

Use the same APIs after joins

After JOIN select, use the same orderBy, page, withTotal, groupBy, and having APIs.

val rows = User().join(Order()) { user, order ->
    leftJoin { user.id == order.userId }
        .select {
            [
                user.id.alias("userId"),
                f.count(order.id).alias("orderCount")
            ]
        }
        .groupBy { user.id }
        .having { f.count(order.id) > 0 }
        .orderBy { it.userId.asc() }
}.page(1, 20).withTotal().toMapList().records

SELECT `user`.`id` AS `userId`,
       COUNT(`order`.`id`) AS `orderCount`
FROM `user`
LEFT JOIN `order`
ON `user`.`id` = `order`.`user_id`
GROUP BY `user`.`id`
HAVING COUNT(`order`.`id`) > :countMin
ORDER BY `user`.`id` ASC
LIMIT 20 OFFSET 0