🐳

[Kotlin] KomapperでFROM句にサブクエリを指定する

2023/11/11に公開

Kotlin

はじめに

随分長い間、FROM句にサブクエリを指定するタイプのクエリをORMでサポートするのは難しいなと感じていたのですが、思い立って頑張ってみたらいい感じに作れたので軽く紹介したいと思います。

この機能は、KotlinのORMであるKomapperの次のバージョン（v1.15.0）でリリース予定です。

利用例と解説

以下は、部署に所属する従業員の給与を部署ごとに集計して部署名と合計給与を算出するSQLです。

select 
    t1_.department_name, 
    t0_.salary 
from (
    select 
        t2_.department_id as department_id, 
        sum(t2_.salary) as salary 
    from 
        employee as t2_ 
    group by 
        t2_.department_id
    ) as t0_ 
inner join 
    department as t1_ 
    on (t0_.department_id = t1_.department_id)

（サブクエリを使わなくても実現できそうではありますが、気にしない方向で）

上記のSQLをKomapperを使ってKotlinのコードで書いたのが次のものです。

// 部署テーブル
val d = Meta.department
// 従業員テーブル
val e = Meta.employee
// 導出テーブル(サブクエリに対応）
val t = Meta.salaryTable

// サブクエリ
val subquery = QueryDsl.from(e)
    .groupBy(e.departmentId)
    .select(e.departmentId, sum(e.salary))

// クエリ本体
val query = QueryDsl.from(t, subquery)
    .innerJoin(d) { t.departmentId eq d.departmentId}
    .select(d.departmentName, t.salary)

// クエリの実行
val list: List<Pair<String?, BigDecimal?>> = db.runQuery(query)

// 結果の出力
// [(ACCOUNTING, 8750.00), (RESEARCH, 10875.00), (SALES, 9400.00)]
println(list)

ポイントは、サブクエリの結果を型付けできるように導出テーブルのためのクラスを用意していることです。このクラスがあることでサブクエリのSELECTリストにあるsalaryやdepartmentIdにタイプセーフにアクセスできます。

導出テーブルのMeta.salaryTableを取得できるようにするには、以下のコードを書いてコードを生成します（コードはKSPによりコンパイル時生成されます）。

@KomapperEntity
data class SalaryTable(
    @KomapperId(virtual = true)
    val departmentId: Int,
    val salary: BigDecimal,
)

アノテーションなどは通常のエンティティ定義に使うものが使えます。 @KomapperId(virtual = true) は実質的に一意となるカラムに付与します（複数カラムに注釈できます）。

サブクエリで取得できるカラムの型や数や順序に合致するように、クラスのプロパティを宣言する必要があります。合致しない場合は実行時エラーになります。

まとめ

KomapperでサブクエリをFROM句に指定する方法を紹介してみました。サブクエリに対応するクラスを作る必要はありますが、比較的シンプルにクエリを構築できます。

もちろん、クラスを書くのが面倒だという場合はSQLを直接書くのも良いと思います（KomapperはSQLテンプレートもサポートしています）。ただ、SQLの実行はSQLで書くのが楽でも、結果をクラスに詰めたりなどは結局面倒ですよね。簡単なSQLはORMで構築し、複雑なSQLは直接書くなど併用が良いかもしれません。

おまけ

コードを少し変えると部署テーブルと導出テーブルに関連を持たせられます。上述の例では、最終的な結果をList<Pair<String?, BigDecimal?>>で受けて出力していますが、以下のようにエンティティでデータを扱うことが可能です。

// クエリ本体
val query = QueryDsl.from(t, subquery)
    .innerJoin(d) { t.departmentId eq d.departmentId}
    .includeAll()

// クエリの実行
val store = db.runQuery(query)

// 結果の走査と出力
store.oneToOne(d, t).entries.forEach { (department, salaryTable) -> 
    println(department.departmentName to salaryTable?.salary)
}

ORMでSQLを組み立てることの利点と言えるでしょう。

はじめに

利用例と解説

まとめ

おまけ

Discussion