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 ユーザーはぜひご参加ください！！
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
}
実装
順を追って説明していきます。
サブクエリ部分を定義する
同一クラス内に書いても良いのですが、ここでは object に分けておくことにします。
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]を利用する必要があるという感じです。
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 にプルリク投げた
本家のドキュメントが貧弱なので今回の記事の内容をプルリクに出しました。
メンバー募集中！
サーバーサイド Kotlin コミュニティを作りました！
Kotlin ユーザーはぜひご参加ください！！
また関西在住のソフトウェア開発者を中心に、関西エンジニアコミュニティを一緒に盛り上げてくださる方を募集しています。
よろしければ Conpass からメンバー登録よろしくお願いいたします。
Discussion