Zenn
🦜

Kotlin製ORMのexposedでテーブルとサブクエリをJOINする

2023/08/26に公開
Kotlin
Ktor
exposed
tech

TL;DR

以下のような SQL を exposed を使って実行する方法を解説します。

select
    DEPARTMENT.department_name,
    dptcnt.cnt
from
    DEPARTMENT INNER JOIN (
        select
            department_id,
            count(employee_id) cnt
        from
            EMPLOYEE
        group by
            department_id
    ) dptcnt ON DEPARTMENT.department_id = dptcnt.department_id

メンバー募集中！

サーバーサイド Kotlin コミュニティを作りました！

Kotlin ユーザーはぜひご参加ください！！

https://serverside-kt.connpass.com/

What's exposed ?

Kotlin 製の ORM（Object-Relational Mapping）ライブラリです。

wiki が提供されていますが結構情報が貧弱で、サブクエリを使った JOIN の方法などが書かれていないため、ハマりポイントになっています。

https://github.com/JetBrains/Exposed/wiki

使うテーブルの定義

それぞれこんな感じの想定です。

DepartmentTable.kt
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
}
EmployeeTable.kt
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
}

実装

順を追って説明していきます。

サブクエリ部分を定義する

同一クラス内に書いても良いのですが、ここでは object に分けておくことにします。

EmployeeCntSubQuery.kt
import org.jetbrains.exposed.sql.JoinType
import org.jetbrains.exposed.sql.QueryAlias
import org.jetbrains.exposed.sql.alias
import org.jetbrains.exposed.sql.count
import org.jetbrains.exposed.sql.select

object EmployeeCntSubQuery {
    val employeeCnt = EmployeeTable.employeeId.count().alias("cnt")
    fun getSubQuery(): QueryAlias {
        return EmployeeTable
        .slice(EmployeeTable.departmentId, employeeCnt)
            .selectAll()
            .groupBy(EmployeeTable.departmentId)
            .alias("dptcnt")
    }
}

ここでポイントなのが、employeeCntとQueryAliasです。

これらがなぜ必要になるか、次の大元のクエリと一緒に説明します。

employeeCntに関しては、サブクエリ内部のcount(employee_id) cntを表現するにも利用できるという感じです。

サブクエリを JOIN

先にalias("dptcnt")でQueryAliasを取得できるようにしていました。

これにより、employeeCntSubQuery[EmployeeCntSubQuery.employeeCnt]と書けるようになります。

また ResultSet のデータにアクセスするためにも、employeeCntSubQuery[EmployeeCntSubQuery.employeeCnt]を利用する必要があるという感じです。

DepartmentDataAccessor.kt
import org.jetbrains.exposed.sql.JoinType
import org.jetbrains.exposed.sql.Query
import org.jetbrains.exposed.sql.QueryAlias
import org.jetbrains.exposed.sql.select

class DepartmentDataAccessor {
    fun getEmployeeCountGroupByDepartment() {
        val employeeCntSubQuery = EmployeeCntSubQuery.getSubQuery()
        val result = DepartmentTable.join(
            employeeCntSubQuery,
            JoinType.INNER,
            additionalConstraint = {
                DepartmentTable.departmentId eq employeeCntSubQuery[EmployeeTable.departmentId]
            }
        ).slice(
            DepartmentTable.departmentName,
            employeeCntSubQuery[EmployeeCntSubQuery.employeeCnt],
        ).selectAll().toList().map{
            resultRow ->
                Pair(resultRow[DepartmentTable.departmentId], resultRow[employeeCntSubQuery[EmployeeCntSubQuery.employeeCnt]])
        }
    }
}

おわりに

サブクエリを作って.alias(xxx)ものを複数用意すれば、複数テーブルと複数サブクエリの JOIN も可能です。

JoinType.LEFTのように記述すれば外部結合も可能となります。

また、この記事を書くにあたって以下の記事を参考にさせていただきました 🙏

exposed の Wiki にプルリク投げた

本家のドキュメントが貧弱なので今回の記事の内容をプルリクに出しました。

https://github.com/Tapac/exposed-wiki/pull/31

メンバー募集中！

サーバーサイド Kotlin コミュニティを作りました！

Kotlin ユーザーはぜひご参加ください！！

https://serverside-kt.connpass.com/

また関西在住のソフトウェア開発者を中心に、関西エンジニアコミュニティを一緒に盛り上げてくださる方を募集しています。

よろしければ Conpass からメンバー登録よろしくお願いいたします。

https://blessingsoftware.connpass.com/

Discussion