🐩

[Kotlin] KomapperでCTE（Common Table Expression）を使う

2023/11/23に公開

Kotlin

はじめに

Kotlin向けのORMであるKomapper v1.15.0でCTE（Common Table Expression = 共通テーブル式）をサポートしました。

どんな感じで使えるのか紹介したいと思います。

お題

以下のSQLをKomapperから発行してみます。（ChatGPTにお題を出してもらいました）

WITH LatestOrders AS (
    SELECT 
        customer_id, 
        MAX(order_date) AS latest_order_date
    FROM orders
    GROUP BY customer_id
)
SELECT 
    c.customer_id, 
    c.name, 
    c.email, 
    o.order_id, 
    o.order_date, 
    o.amount
FROM customers c
JOIN LatestOrders lo ON c.customer_id = lo.customer_id
JOIN orders o ON c.customer_id = o.customer_id AND lo.latest_order_date = o.order_date;

このSQLでは、LatestOrdersというCTEを定義しています。このCTEは、各顧客ごとに最新の注文日を集計します。その後、メインのクエリでcustomersテーブルとordersテーブルを結合し、CTEで特定された最新の注文に関する情報を取得しています。

テーブル定義とデータ

次のようなテーブル定義を使います。

顧客テーブル

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

注文テーブル

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    amount DECIMAL(10, 2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

事前に投入しておくデータは次の通りです。

顧客テーブルデータ

INSERT INTO customers (customer_id, name, email) VALUES
(1, '山田太郎', 'taro.yamada@example.com'),
(2, '鈴木花子', 'hanako.suzuki@example.com'),
(3, '佐藤一郎', 'ichiro.sato@example.com');

注文テーブルデータ

INSERT INTO orders (order_id, customer_id, order_date, amount) VALUES
(101, 1, '2023-01-15', 1200.00),
(102, 2, '2023-01-17', 800.00),
(103, 1, '2023-02-10', 1500.00),
(104, 3, '2023-02-20', 2000.00),
(105, 2, '2023-03-05', 700.00),
(106, 3, '2023-03-12', 950.00),
(107, 1, '2023-03-15', 1300.00);

エンティティクラスの定義

テーブルに対応するエンティティクラスを2つ作ります。

顧客エンティティ

@KomapperEntity
@KomapperTable("customers")
data class Customer(
    @KomapperId
    val customerId: Int,
    val name: String,
    val email: String
)

注文エンティティ

@KomapperEntity
@KomapperTable("orders")
data class Order(
    @KomapperId
    val orderId: Int,
    val customerId: Int,
    val orderDate: LocalDate,
    val amount: BigDecimal
)

CTEであるLatestOrdersに対応するエンティティクラスも作ります。

最新注文エンティティ

@KomapperEntity
@KomapperTable("latest_orders")
data class LatestOrder(
    @KomapperId
    val customerId: Int,
    val latestOrderDate: LocalDate,
)

Kotlinコードの実行

クエリを組み立てて実行して結果を出力するコード全体は次のようになります。

fun main() {
    // データベースインスタンスの生成
    val db = JdbcDatabase("jdbc:h2:mem:example;DB_CLOSE_DELAY=-1")

    // テーブル定義とデータの作成
    db.runQuery {
        QueryDsl.executeScript(ddl + data)
    }
    
    // メタモデルの取得
    val c = Meta.customer
    val o = Meta.order
    val lo = Meta.latestOrder
    
    // サブクエリの作成
    val subquery = QueryDsl.from(o)
        .groupBy(o.customerId)
        .select(o.customerId, max(o.orderDate))
    
    // クエリを作成（WITH句でCTEを使っている）
    val query = QueryDsl.with(lo, subquery).from(c)
        .innerJoin(lo) { c.customerId eq lo.customerId }
        .innerJoin(o) {
            c.customerId eq o.customerId
            lo.latestOrderDate eq o.orderDate
        }
        .include(c, o)
    
    // クエリの実行
    val store = db.runQuery(query)
    
    // 結果の出力（結果に含まれる顧客と注文の多重度は1対1なのでoneToOne関数で取り出している）
    for((customer, order) in store.oneToOne(c, o)) {
        println(customer)
        println(order)
        println()
    }
}

クエリはWITH句に指定しているサブクエリと本体のクエリで2つに分けています。
SQLのように一つにまとめることは可能ですが、分けておいた方が読みやすいと思います。

発行されるSQL

上述のコードを動かした際に発行されるSQLです（フォーマットとしています）。

with latest_orders(customer_id, latest_order_date) as(
    select
        t2_.customer_id,
        max(t2_.order_date)
    from
        orders as t2_
    group by
        t2_.customer_id
)
select
    t0_.customer_id,
    t0_.name,
    t0_.email,
    t2_.order_id,
    t2_.customer_id,
    t2_.order_date,
    t2_.amount
from customers as t0_
inner join latest_orders as t1_ on (t0_.customer_id = t1_.customer_id)
inner join orders as t2_ on (t0_.customer_id = t2_.customer_id and t1_.latest_order_date = t2_.order_date)

細かい部分に違いはありますが、意味的にはお題のSQLと同等のものが発行されていますね！

出力結果

コンソールへの出力結果は以下の通りです。

Customer(customerId=2, name=鈴木花子, email=hanako.suzuki@example.com)
Order(orderId=105, customerId=2, orderDate=2023-03-05, amount=700.00)

Customer(customerId=3, name=佐藤一郎, email=ichiro.sato@example.com)
Order(orderId=106, customerId=3, orderDate=2023-03-12, amount=950.00)

Customer(customerId=1, name=山田太郎, email=taro.yamada@example.com)
Order(orderId=107, customerId=1, orderDate=2023-03-15, amount=1300.00)

顧客ごとの最新の注文に関する情報が出力されています。

まとめ

10年くらい前はCTEをサポートしているデータベースはまだ少なかった記憶がありますが、最近はかなりサポートされているんですね。Komapperがサポートしているデータベースで言えば、H2、MariaDB、MySQL v8.x、PostgreSQL、Oracle、SQL Serverなどで動作しました。

今回は触れませんでしたが、複数のCTEを同時に使ったりWITH RECURSIVEを使った再帰クエリなどもサポートしています。

再帰クエリのサンプルコードに興味があればこちらを参照ください。

Komapperはv1.15.0でWindow関数もサポートしています。CTEと組み合わせることで表現力が高まると思います。是非お試しください。