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

## 今回の目的

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


```sql
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`同士を結合するための中間テーブルの役割をもっています。


<img width="371" alt="スクリーンショット 2018-06-11 10.40.01.png" src="https://qiita-image-store.s3.amazonaws.com/0/53540/03a80c5c-b724-042d-a99a-87c84e08997b.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`も思った通りにできました。

```sql
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を展開しています。

```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_id`と`family_id`がどちらも`user`テーブルの`id`と関連づいている為、どちらを使うかをプログラムが判断できなかった為に発生したと思われます。

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

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

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

---

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