Help us understand the problem. What is going on with this article?

Kotlin製 ORM の Exposed で、 SQL の JOIN を DSLでやってみる

今回の目的

今回のゴールはExposedを使って、次のようなJOINした結果を取得したいというものです。

mysql> SELECT * FROM user AS u LEFT OUTER JOIN family AS f ON u.id = f.user_id LEFT OUTER JOIN user AS u2 ON f.family_id = u2.id;
+----+---------------+-----+---------+---------+-----------+--------------+----+----------------+-----+---------+
| id | name          | age | address | user_id | family_id | relationship | id | name           | age | address |
+----+---------------+-----+---------+---------+-----------+--------------+----+----------------+-----+---------+
|  1 | ユーザー1       |  36 | 東京     |       1 |        10 | 長男         | 10 | ユーザー10       |  11 | 不定     |
|  1 | ユーザー1       |  36 | 東京     |       1 |        11 | 次男         | 11 | ユーザー11       |  10 | 不定     |
+----+---------------+-----+---------+---------+-----------+--------------+----+----------------+-----+---------+

テーブル構成

まずテーブル構成について説明します。
次のようにuserfamilyテーブルがあります(user'userテーブルです)。
familyテーブルはuser同士を結合するための中間テーブルの役割をもっています。

スクリーンショット 2018-06-11 10.40.01.png

DSLによるJOINの実装

ここではまずうまくいくやり方を紹介します。
ダメな例は後述しています。

sample.kt

@Transactional
fun getWithFamily(id: Int): Unit {
    val u2 = UserTable.alias("u2")
    UserTable
            .leftJoin(otherTable = FamilyTable, onColumn = { UserTable.id }, otherColumn = { FamilyTable.userId })
            .leftJoin(otherTable = u2, onColumn = { FamilyTable.familyId }, otherColumn = { u2.get(UserTable.id) })
            .slice(*UserTable.wildcard(), FamilyTable.relation, *u2.wildcard(u2.get(UserTable.address)))
            .select { UserTable.id eq id }
            .forEach { println(it) }
    }
}

aliasメソッドで、user'側に別名をつけないと、同じテーブルをJOINできません。
さらに、leftJoinメソッドで、JOINするテーブルと対応するカラムをしていしています。

実行結果

実行ログが以下になります。

result.log
SELECT `user`.id, `user`.name, `user`.age, `user`.address, family.relationship, u2.id, u2.name, u2.age FROM `user` LEFT OUTER JOIN family ON `user`.id = family.user_id LEFT JOIN `user` u2 ON family.family_id = u2.id WHERE `user`.id = 1
family.relationship=長男, u2.name=ユーザー10, `user`.id=1, u2.age=11, `user`.address=東京, u2.id=10, `user`.name=ユーザー1, `user`.age=36
family.relationship=次男, u2.name=ユーザー11, `user`.id=1, u2.age=10, `user`.address=東京, u2.id=11, `user`.name=ユーザー1, `user`.age=36

SQL文を整形して見やすくします。SELECTするカラムやJOINも思った通りにできました。

SELECT 
    `user`.id, `user`.name, `user`.age, `user`.address, 
    family.relationship,
    u2.id, u2.name, u2.age 
FROM `user` 
    LEFT JOIN family ON `user`.id = family.user_id 
    LEFT JOIN `user` u2 ON family.family_id = u2.id 
WHERE `user`.id = 1

実行結果は、Hibernateのように one-to-many は構造化されず、普通のSQLを実行した時のように結果が取れることがわかります。
構造化は自分で実装する必要があります。この辺はDoma2っぽいです。

Tableのフィールドを列挙する便利メソッド

上の例で、.slice(*UserTable.wildCard()・・・ と書いている所がありますが、
sliceメソッドは SQLSELECTで取得するカラムを指定できるものです。
全部取得するう場合は書く必要ないのですが、TableAは全カラム、TableBは一部のカラムのように指定したい場合、本来以下のように列挙して書かないといけない為、コードが無駄に長くなったり、フィールド追加時に漏れたりということがありえます。

slice(TableA.a, TableA.b, TableA.c, TableA.d, TableB.a)

そこで、Tableクラスの拡張メソッドを作成し、Table内のフィールドを列挙して返すwildcardメソッドを作成しました。これでカラム数が多いテーブルを扱う際は結構コード量の削減ができます。
使う際は *(アスタリスク) を前に付ける事で、Arrayを展開しています。

util.kt
/**
 * <@see Table> 内の全 field の <@see Array> を返します。
 *
 * @param excludes: 除外する field を指定
 *
 * 利用例) select 時のカラム指定をテーブル単位で全て列挙させる
 *   Table1
 *       .leftJoin(Table2)
 *       .slice(*Table1.wildCard(), Table2.field1, Table2.field2)
 *       .selectAll()
 */
fun<T : Table> T.wildcard(vararg excludes: Expression<*>): Array<Expression<*>> = fields.filter { !excludes.contains(it) } .toTypedArray()

ダメな例

ちなみにダメな例も紹介しておきます。
Exposedの公式でJOINをする場合は (Table1 leftJoin Table2)みたいにやればできると書いているので、次のように実装しました。

sample.kt
@Transactional
fun getWithFamily(id: Int): Unit {
    (UserTable leftJoin FamilyTable leftJoin UserTable).select { UserTable.id eq id }
}

これで実行すると、次のようなエラーが発生します。

error.log
javax.ws.rs.ext.ExceptionMapper - UnhandledException
java.lang.IllegalStateException: Cannot join with FamilyTable@b3985b64 as there is multiple primary key <-> foreign key references.
UserTable.id -> FamilyTable.user_id, FamilyTable.family_id

これは恐らく familyテーブルのuser_idfamily_idがどちらもuserテーブルのidと関連づいている為、どちらを使うかをプログラムが判断できなかった為に発生したと思われます。

なので、先に書いた成功例では、次のようにどのカラムを関連付けるかを書くことで、解決をさせています。

sample.kt
.leftJoin(otherTable = FamilyTable, onColumn = { UserTable.id }, otherColumn = { FamilyTable.userId })

もっと単純なJOINですむ場合は当然(Table1 leftJoin Table2)で出来ます。


軽く ExposedJOINを見てみるつもりが、思わずガッツリ深掘りすることになってしまいましたが、その分理解が深まりました。
とりあえずやろうと思えば大概のSQLは発行できそうです。

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away