Aggregates with Jetbrains Exposed SQL Framework

March 10, 2019 2 minutes reading time Development kotlin sql

Since I currently generate the web pages for this blog with a static website generator called Hugo, I will have no out-of-the-box commenting solution. Except I integrate an existing solution, e.g. Disqus. Others are mentioned on Hugo’s website as well.

I’ve looked at some of them, but haven’t found one that I liked. As a consequence, I’ve to either live with no comments or roll my own solution. So I decided to create one myself.

I want to share one detail I ran into while using Jetbrains Exposed SQL framework to implement comments for this website. For administration purposes, I want to have a page where I can see a summary of all posts with comments, the posts with the most recent comments on top. The comment table holds all comments, with a field comment_id with an auto-generated comment id, a field post_id holding the id of the post for which the comment was made, a comment_timestamp field, and some more.

A SQL statement for getting the required information looks like this:

SELECT
  post_id,
  COUNT(comment_id),
  MAX(comment_timestamp)
FROM comments
GROUP BY post_id
ORDER BY MAX(comment_timestamp) DESC

The problem I ran into was addressing the columns containing the aggregated values in the result. The only place I found useful information was the Issues pages for Exposed on GitHub.

This is the above SQL as a Kotlin function using Exposed. It creates a collection of Summary data class instances containing the information that I need.

fun getPostsWithComments() = transaction {
    val idColumn = CommentTable.postId
    val countColumn = CommentTable.commentId.count()
    val maxColumn = CommentTable.commentTimestamp.max()
    val resultSet = CommentTable
            .slice(idColumn, countColumn, maxColumn)
            .selectAll()
            .groupBy(idColumn)
            .orderBy(maxColumn, SortOrder.DESC)
    val list = ArrayList<Summary>()
    resultSet.mapTo(list) { record ->
        Summary(
                postId = record[idColumn],
                comments = record[countColumn],
                latest = record[maxColumn]
        )
    }
}

The take-away from this is: define your aggregate columns first, then reuse them for creating the select and addressing the fields in the resultSet.

Update: This blog has no commenting functionality anymore.