Aggregate & Group

Aggregation

Java

BookTable table = Tables.BOOK_TABLE;

long count = sqlClient
    .createQuery(table)
    .where(table.name().ilike("graphql"))
    .select(
        table
            .asTableEx().authors().id()
            .count(true) // distinct: true
    )
    .fetchOne();

Kotlin

val count = sqlClient
    .createQuery(Book::class) {
        where(table.name.ilike("graphql"))
        select(
            count(
                table.asTableEx().authors.id,
                distinct = true
            )
        )
    }
    .fetchOne()

The generated SQL is:

select
    // highlight-next-line 
    count(distinct tb_2_.AUTHOR_ID)
from BOOK tb_1_
inner join BOOK_AUTHOR_MAPPING tb_2_
    on tb_1_.ID = tb_2_.BOOK_ID
where
    lower(tb_1_.NAME) like ? /* %graphql% */

Grouping

Java

BookTable table = Tables.BOOK_TABLE;

List<Tuple2<Long, BigDecimal>> tuples = sqlClient
    .createQuery(table)
    .groupBy(table.storeId()) ❶
    .select(
            table.storeId(), ❷
            table.price().avg() ❸
    )
    .execute();

Kotlin

val tuples: List<Tuple2<Long, BigDecimal>> = sqlClient
    .createQuery(Book::class) {
        groupBy(table.store.id) ❶
        select(
            table.store.id, ❷
            avg(table.price).asNonNull() ❸
        )
    }
    .execute()

• ❶ Group by STORE_ID, the foreign key of BOOK table

  info

  Here Jimmer does not treat table.store as a join operation, but considers table.store.id as a whole as the foreign key field.

  Please see phantom joins.

• ❷ Grouping columns can be directly queried.

• ❸ Non-grouping columns can only be queried as parameters of aggregate functions.

  caution

  The Kotlin code has an extra function call: asNonNull().

  In Jimmer Kotlin DSL, aggregate functions like avg (also sum, min, max) return Nullable types. For a table with no data, aggregating any column would return null without grouping.

  However this is not the case when used with grouping. After grouping, each group has at least one row internally, if the original field being aggregated is non-null itself, the result after aggregation would not be null.

  So here asNonNull() converts the Nullable expression to NonNull, such that execute() returns List<Tuple2<Long, BigDecimal>>, same as the type explicitly specified for tuples in the first line.

  If asNonNull() is removed, execute() would return List<Tuple2<Long, BigDecimal?>>, causing compile error.