Kotlin製ORMのexposedでselectの対象としてサブクエリを利用する
TL;DR
以下のような SQL を exposed を使って実行する方法を解説する。
select
dpt.department_id,
dpt.department_name,
(select MAX(employee_id) from EMPLOYEE emp where emp.department_id = dpt.department_id) AS latest_employee_id
from
DEPARTMENT dpt
「普通に JOIN すればいいじゃん、、、」という感じですが、移行プロジェクトなどの場合元の SQL をそのまま移植する必要があり、今回たまたまそういう機会にぶち当たりました。
自分の引き出しとしてのメモと、同じことで困ってる人のために記事を書きます。
メンバー募集中！
サーバーサイド Kotlin コミュニティを作りました！
Kotlin ユーザーはぜひご参加ください！！
What's exposed ?
Kotlin 製の ORM（Object-Relational Mapping）ライブラリです。
wiki が提供されていますが結構情報が貧弱で、サブクエリを使った JOIN の方法などが書かれていないため、ハマりポイントになっています。
使うテーブルの定義
それぞれこんな感じの想定です。
import org.jetbrains.exposed.sql.Table
import org.jetbrains.exposed.sql.javatime.datetime
object DepartmentTable : Table("DEPARTMENT") {
val departmentId = varchar("DEPARTMENT_ID", 10) //部署ID
val departmentName = varchar("DEPARTMENT_NAME", 256) //部署ID
}
import org.jetbrains.exposed.sql.Table
import org.jetbrains.exposed.sql.javatime.datetime
object EmployeeTable : Table("EMPLOYEE") {
val employeeId = varchar("EMPLOYEE_ID", 10) //社員ID
val departmentId = varchar("DEPARTMENT_ID", 10) //部署ID
}
実装
以前出したexposed でテーブルとサブクエリを JOIN するの記事のように Alias を作ってあげるところは想像がつくかと思います。
object LatestEmployeeSubQuery {
val latestEmployeeId = EmployeeTable.employeeId.max()
val latestEmployeeSubQuery = EmployeeTable.slice(
latestEmployeeId,
).select(EmployeeTable.departmentId eq DepartmentTable.departmentId).alias("latestEmployeeId")
}
これであとは「大元のコードのsliceの中にlatestEmployeeSubQueryを書いたら終わりでしょ」といきたいところ。
class DepartmentDataAccessor {
fun getLatestEmployeeIdByDepartment() {
val result = DepartmentTable.slice(
DepartmentTable.departmentId,
DepartmentTable.departmentName,
LatestEmployeeSubQuery.latestEmployeeSubQuery,
).selectAll().toList()
}
}
しかしそうは問屋がおろさないんですよね、、、
このコードは Expression 型に対して Alias 型が渡ってきています的なエラーになります。
このエラーを解消するために、QueryAlias型をExpression型へ変換する拡張関数を定義してあげる必要があります。
class SubQueryExpression<T>(private val aliasQuery: QueryAlias) : Expression<T>() {
override fun toQueryBuilder(queryBuilder: QueryBuilder) {
aliasQuery.describe(TransactionManager.current(), queryBuilder)
}
}
そして、ここで定義したSubQueryExpressionをLatestEmployeeSubQueryで使います。
object LatestEmployeeSubQuery {
val latestEmployeeId = EmployeeTable.employeeId.max()
val latestEmployeeSubQuery = EmployeeTable.slice(
latestEmployeeId,
).select(EmployeeTable.departmentId eq DepartmentTable.departmentId).alias("latestEmployeeId")
+ val latestEmployeeIdExp = SubQueryExpression<String>(latestEmployeeSubQuery)
}
このように Expression 型に変換したサブクエリを変数に保存しておき、DataAccessor で利用すると怒られなくなります。
class DepartmentDataAccessor {
fun getLatestEmployeeIdByDepartment() {
val result = DepartmentTable.slice(
DepartmentTable.departmentId,
DepartmentTable.departmentName,
- LatestEmployeeSubQuery.latestEmployeeSubQuery,
+ LatestEmployeeSubQuery.latestEmployeeIdExp,
).selectAll().toList()
}
}
あとは以下のようにして DB から取得したサブクエリの結果を取り出すことができます。
class DepartmentDataAccessor {
fun getLatestEmployeeIdByDepartment() {
val result = DepartmentTable.slice(
DepartmentTable.departmentId,
DepartmentTable.departmentName,
LatestEmployeeSubQuery.latestEmployeeIdExp,
).selectAll().toList()
+ .map{
+ resultRow ->
+ Pair(resultRow[DepartmentTable.departmentId], resultRow[LatestEmployeeSubQuery.latestEmployeeIdExp])
+ }
}
}
おわりに
この記事を書くにあたって以下の記事を参考にさせていただきました 🙏
メンバー募集中！
サーバーサイド Kotlin コミュニティを作りました！
Kotlin ユーザーはぜひご参加ください！！
また関西在住のソフトウェア開発者を中心に、関西エンジニアコミュニティを一緒に盛り上げてくださる方を募集しています。
よろしければ Conpass からメンバー登録よろしくお願いいたします。
Discussion