ExposedでDATE_FORMAT関数を使ってグループ化する

環境

• exposed: 0.28.1
• DB: mysql

実装

DateFormatFunction.kt

import org.jetbrains.exposed.sql.CharColumnType
import org.jetbrains.exposed.sql.ExpressionWithColumnType
import org.jetbrains.exposed.sql.QueryBuilder
import org.jetbrains.exposed.sql.append
import org.joda.time.DateTime

class DateFormatFunction<T : ExpressionWithColumnType<DateTime>>(private val exp: T, private val format: String) :
    org.jetbrains.exposed.sql.Function<String>(CharColumnType()) {
    override fun toQueryBuilder(queryBuilder: QueryBuilder) = queryBuilder {
        append("DATE_FORMAT(", exp, ", '$format')")
    }
}

日別のコミット数を取得するコード

Example.kt

val dateFormatFunc = DateFormatFunction(Commits.createdAt, "%Y/%m/%d")
Commits.slice(dateFormatFunc, Commits.id.count())
    .selectAll()
    .groupBy(dateFormatFunc)
    .orderBy(Commits.createdAt, SortOrder.ASC)
    .toList()

実行されるSQL

SELECT DATE_FORMAT(commits.created_at, '%Y/%m/%d'), COUNT(commits.id)
FROM commits 
GROUP BY DATE_FORMAT(commits.created_at, '%Y/%m/%d') 
ORDER BY commits.created_at ASC

参考

• https://github.com/JetBrains/Exposed/issues/367