SQL allows you to do calculations on columns over multiple rows using aggregate functions like COUNT, SUM, AVG etc. This post explains how to use them in Kotlin Exposed. We will also cover arithmetic operations on one or more columns. If you haven’t used Kotlin Exposed before, you can go here for an introduction.

Consider an application containing two tables in an SQL database: Comment and User. A comment is written by a user, and can receive likes/dislikes. The snippet below shows the table definitions:

object UserTable: Table("user") {
    val id = long("id").autoIncrement()
    val name = varchar("owner_type", 64)

    override val primaryKey = PrimaryKey(id)
}

object CommentTable: Table("comment") {
    val id = long("id").autoIncrement()
    val fromUser = long("from_user_id").references(UserTable.id)
    val content = text("content")

    val likes = long("likes").default(0L)
    val dislikes = long("dislikes").default(0L)

    override val primaryKey = PrimaryKey(id)
}

To print out all the comments, you can simply write:

CommentTable
    .innerJoin(UserTable)
    .selectAll()
    .map { row ->
        println("""
            user = ${row[UserTable.name]},
            content = ${row[CommentTable.content]},
            likes = ${row[CommentTable.likes]},
            dislikes = ${row[CommentTable.dislikes]}""")
    }

The SUM aggregate function in SQL can be used to collect the total number of likes/dislikes of each user. In Exposed we can do the same by calling .sum() on a column.

CommentTable
    .innerJoin(UserTable)
    .slice(UserTable.name, CommentTable.likes.sum(), CommentTable.dislikes.sum())
    .selectAll()
    .groupBy(UserTable.name)
    .map { row ->
        println("""
            user = ${row[UserTable.name]},
            totalLikes = ${row[CommentTable.likes.sum()] ?: -1},
            totalDislikes = ${row[CommentTable.dislikes.sum()] ?: -1}""")
    }

In Exposed we can also perform arithmetic on columns using the + - * / and % operators. The result is an expression object that can be used in the query. For example, let’s define a 'popularity score' for each user which is simply the sum of all total likes - total dislikes.

Please note! You do need to import them before you can use them (normally your development environment would suggest an import, but it does not find these automatically)

import org.jetbrains.exposed.sql.SqlExpressionBuilder.minus

//...

val commentPopularityScore = CommentTable.likes - CommentTable.dislikes

CommentTable
    .innerJoin(UserTable)
    .slice(UserTable.name, commentPopularityScore.sum())
    .selectAll()
    .groupBy(UserTable.name)
    .map { row ->
        println("""
            user = ${row[UserTable.name]},
            popularityScore = ${row[commentPopularityScore.sum()] ?: 0L} """)
    }

The commentPopularityScore can also be used in a having clause to only select users that have a certain score. To only select users with a higher popularity score than 5, you could use the following code:

CommentTable
    .innerJoin(UserTable)
    .slice(UserTable.name, commentPopularityScore.sum())
    .selectAll()
    .groupBy(UserTable.name)
    .having { commentPopularityScore.sum() greater 5L }
    .map { row ->
        println("""
            user = ${row[UserTable.name]},
            popularityScore = ${row[commentPopularityScore.sum()] ?: 0L}
        """.trimIndent())

        UserPopularityScore(
            fromUser = row[UserTable.name],
            popularityScore = row[commentPopularityScore.sum()] ?: 0L
        )
    }

You can test it out yourself in this fully working example using H2 here: github.com/toefel18/kotlin-exposed-blog-aggregate-functions

shadow-left