今回の目的
今回のゴールは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 | 不定 |
+----+---------------+-----+---------+---------+-----------+--------------+----+----------------+-----+---------+
テーブル構成
まずテーブル構成について説明します。
次のようにuserとfamilyテーブルがあります(user'はuserテーブルです)。
familyテーブルはuser同士を結合するための中間テーブルの役割をもっています。
DSLによるJOINの実装
ここではまずうまくいくやり方を紹介します。
ダメな例は後述しています。
@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するテーブルと対応するカラムをしていしています。
実行結果
実行ログが以下になります。
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メソッドは SQLのSELECTで取得するカラムを指定できるものです。
全部取得するう場合は書く必要ないのですが、TableAは全カラム、TableBは一部のカラムのように指定したい場合、本来以下のように列挙して書かないといけない為、コードが無駄に長くなったり、フィールド追加時に漏れたりということがありえます。
slice(TableA.a, TableA.b, TableA.c, TableA.d, TableB.a)
そこで、Tableクラスの拡張メソッドを作成し、Table内のフィールドを列挙して返すwildcardメソッドを作成しました。これでカラム数が多いテーブルを扱う際は結構コード量の削減ができます。
使う際は *(アスタリスク) を前に付ける事で、Arrayを展開しています。
/**
* <@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)みたいにやればできると書いているので、次のように実装しました。
@Transactional
fun getWithFamily(id: Int): Unit {
(UserTable leftJoin FamilyTable leftJoin UserTable).select { UserTable.id eq id }
}
これで実行すると、次のようなエラーが発生します。
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_idとfamily_idがどちらもuserテーブルのidと関連づいている為、どちらを使うかをプログラムが判断できなかった為に発生したと思われます。
なので、先に書いた成功例では、次のようにどのカラムを関連付けるかを書くことで、解決をさせています。
.leftJoin(otherTable = FamilyTable, onColumn = { UserTable.id }, otherColumn = { FamilyTable.userId })
もっと単純なJOINですむ場合は当然(Table1 leftJoin Table2)で出来ます。
軽く Exposed の JOINを見てみるつもりが、思わずガッツリ深掘りすることになってしまいましたが、その分理解が深まりました。
とりあえずやろうと思えば大概のSQLは発行できそうです。