【ScalikeJDBC】QueryDSLでSELECT パターン別記述例

既存のSQLをQueryDSLで書き直していくにあたり、「これどう書けばいいんだろう」と困ることがあっていろいろ調べたのでメモしておく。

## 例に使うテーブルとクラス定義

#### テーブル
`user`テーブル

| user_id | name | role_id |
|:-:|:-:|:-:|
| 1 | 山田 太郎 | 1 |
| 2 | 田中 一郎 | 2 |
| 3 | 佐藤 史郎 | 2 |

`role`テーブル

| role_id | name |
|:-:|:-:|
| 1 | Administrator |
| 2 | Developer |

userとroleはrole_idで紐付くものとする。

#### クラス定義

```scala
case class User(userId: Int, name: String, roleId: Int)
object User extends SQLSyntaxSupport[User] {
  def apply(rs: WrappedResultSet, rn: ResultName[User]): User = autoConstruct(rs, rn)
}

case class Role(roleId: Int, name: String)
object Role extends SQLSyntaxSupport[Role] {
  def apply(rs: WrappedResultSet, rn: ResultName[Role]): Role = autoConstruct(rs, rn)
}

// ユーザーとロールをJOINした結果を表すクラス
case class UserWithRole(userId: Int, userName: String, roleId: Int, roleName: String)
object UserWithRole {
  def apply(rs: WrappedResultSet, rnUser: ResultName[User], rnRole: ResultName[Role]): UserWithRole =
    UserWithRole(
      rs.int(rnUser.userId),
      rs.string(rnUser.name),
      rs.int(rnRole.roleId),
      rs.string(rnRole.name),
    )
}
```

***

## パターン別記述例

前提として、全ての例において

```scala
val u = User.syntax("u")
val r = Role.syntax("r")
```
が定義されているものとする。
また、DBSessionのimplicit引数は省略している。

***

#### 基本

###### SQL

```sql
SELECT * FROM user
```

###### QueryDSL

```scala
val result = withSQL {
  select(u.result.*).from(User as u)
}.map(User(_, u.resultName)).list().apply()

println(result) // List(User(1,山田 太郎,1), User(2,田中 一郎,2), User(3,鈴木 史郎,2))
```


***

#### 特定のカラムだけ指定

###### SQL

```sql
SELECT role_id FROM user
```

###### QueryDSL

```scala
val result = withSQL {
  select(u.result.roleId).from(User as u)
}.map(_.int(1)).list().apply()

println(result) // List(1, 2, 2)
```

***

#### 重複排除(DISTINCT)

###### SQL

```sql
SELECT DISTINCT role_id FROM user
```

###### QueryDSL

```scala
val result = withSQL {
  select(sqls.distinct(u.result.roleId)).from(User as u)
}.map(_.int(1)).list().apply()

println(result) // List(1, 2)
```

ポイント: 「DISTINCTはどう書けば...？」とか困ったら SQLSyntax(エイリアスはsqls)のメソッドを探せば大体ある。

***

#### WHERE

###### SQL

```sql
SELECT * FROM user
 WHERE user_id < 3
 AND (name = '山田 太郎' OR role_id = 2)
```

###### QueryDSL

```scala
val result = withSQL {
  select(u.result.*).from(User as u)
    .where.lt(u.userId, 3)
    .and.withRoundBracket(_.eq(u.name, "山田 太郎").or.eq(u.roleId, 2))
}.map(User(_, u.resultName)).list().apply()

println(result) // List(User(1,山田 太郎,1), User(2,田中 一郎,2))
```

ポイント: 括弧は`withRoundBracket`, または`sqls.roundBracket`で表現できる。

***

#### GROUP BY

###### SQL

```sql
SELECT COUNT(1) FROM user
 GROUP BY role_id
 HAVING COUNT(1) > 1
```

###### QueryDSL

```scala
val result = withSQL {
  select(sqls.count).from(User as u)
    .groupBy(u.roleId)
    .having(sqls.gt(sqls.count, 1))
}.map(_.int(1)).list().apply()

println(result) // List(2)
```

ポイント: 集計関数(COUNT, MAXとか)もSQLSyntaxにあるので、`sqls.count`等の書き方ができる。

***

#### JOIN

###### SQL

```sql
SELECT u.user_id, u.name, r.role_id, r.name
 FROM user u
 INNER JOIN role r
 ON u.role_id = r.role_id
```

###### QueryDSL

```scala
val result = withSQL {
  select(u.result.userId, u.result.name, r.result.roleId, r.result.name)
    .from(User as u)
    .innerJoin(Role as r)
    .on(u.roleId, r.roleId)
}.map(UserWithRole(_, u.resultName, r.resultName)).list().apply()

println(result) // List(UserWithRole(1,山田 太郎,1,Administrator), UserWithRole(2,田中 一郎,2,Developer), UserWithRole(3,佐藤 史郎,2,Developer))
```

***

#### JOIN (ON句に複数条件指定)

###### SQL

```sql
SELECT u.user_id, u.name, r.role_id, r.name
 FROM user u
 INNER JOIN role r
 ON u.role_id = r.role_id
 AND r.role_id = 2
```

###### QueryDSL

```scala
val result = withSQL {
  select(u.result.userId, u.result.name, r.result.roleId, r.result.name)
    .from(User as u)
    .innerJoin(Role as r)
    .on(u.roleId, r.roleId)
    .append(sqls.and.eq(r.roleId, 2))
}.map(UserWithRole(_, u.resultName, r.resultName)).list().apply()

println(result) // List(UserWithRole(2,田中 一郎,2,Developer), UserWithRole(3,佐藤 史郎,2,Developer))
```

ポイント: ONに条件を複数つけたい場合、appendで対応できる。

こうも書ける。こっちの方がいいかも？

```scala
val result2 = withSQL {
  select(u.result.userId, u.result.name, r.result.roleId, r.result.name)
    .from(User as u)
    .innerJoin(Role as r)
    .on(sqls.eq(u.roleId, r.roleId).and.eq(r.roleId, 2))
}.map(UserWithRole(_, u.resultName, r.resultName)).list().apply()

println(result2) // List(UserWithRole(2,田中 一郎,2,Developer), UserWithRole(3,佐藤 史郎,2,Developer))
```

***

#### サブクエリ(FROM)

###### SQL

```sql
SELECT * FROM (
 SELECT u.user_id, u.name user_name, r.role_id, r.name role_name
  FROM user u
  INNER JOIN role r
  ON u.role_id = r.role_id
) AS t
```

###### QueryDSL

```scala
val t = SubQuery.syntax("t").include(u, r)
val result = withSQL {
  select(u.resultName.userId, u.resultName.name, r.resultName.roleId, r.resultName.name).from(
    select(u.result.userId, u.result.name, r.result.roleId, r.result.name)
      .from(User as u)
      .innerJoin(Role as r)
      .on(u.roleId, r.roleId) as t
  )
}.map(UserWithRole(_, u.resultName, r.resultName)).list().apply()

println(result) // List(UserWithRole(1,山田 太郎,1,Administrator), UserWithRole(2,田中 一郎,2,Developer), UserWithRole(3,佐藤 史郎,2,Developer))
```

ポイント: `SubQuery.syntax`でサブクエリ結果のエイリアスにあたるものを作成できる。メインのselectに指定するカラム名には`resultName`をつける。(理由は[後述](#おまけ2-uresult-uresultname-u-の違い))

***

#### サブクエリ(WHERE)

###### SQL

```sql
SELECT * FROM user
WHERE user_id = (
 SELECT user_id FROM user
 WHERE role_id = 1
)
```

###### QueryDSL

```scala
val result = withSQL {
  val a = select[User](u.result.*).from(User as u)
    .where.eq(u.userId,
      sqls.roundBracket(select(u.result.userId)
        .from(User as u)
        .where.eq(u.roleId, 1)
        .sql
      )
  )
  println(a.sql.value)
  a
}.map(User(_, u.resultName)).list().apply()

println(result) // List(User(1,山田 太郎,1))
```

ポイント: サブクエリを括弧で囲むためには`sqls.roundBracket`を使う。
`sqls.roundBracket`は引数にSQLSyntaxをとるので、SQLBuilder(select~)の最後に`.sql`をつけてSQLSyntaxに変換する。
ちなみに、`.where.in`の場合は第2引数にSQLBuilder自体を渡せるので、`.sql`でSQLSyntaxに変換する必要も`sqls.roundBracket`で囲む必要もない。

***

### おまけ1: 実際に発行されているSQLをみる方法
SQLBuilderで組み立てたSQLは、カラムに別名がつけられたりしていて想定と異なる場合がある。
発行されているSQLは `SQLBuilder.sql.value` で取得できる。

```scala
val sqlBuilder = select(u.result.*).from(User as u)
println(sqlBuilder.sql.value) 

// select u.user_id as ui_on_u, u.name as n_on_u, u.role_id as ri_on_u from user u
```

### おまけ2: `u.result.*`, `u.resultName.*`, `u.*` の違い
それぞれ発行されるSQLのカラム名の記述が異なる。

`u.result.*` はカラム名 + 別名の形になる。たいていはこの書き方が一番エラーになりにくい。

```scala
val sqlBuilder1 = select(u.result.*).from(User as u)
println(sqlBuilder1.sql.value)
// select u.user_id as ui_on_u, u.name as n_on_u, u.role_id as ri_on_u from user u
```

`u.resultName.*` は別名のみになるため、通常はUnknown Columnでエラーとなる。

```scala
val sqlBuilder2 = select(u.resultName.*).from(User as u)
println(sqlBuilder2.sql.value)
// select ui_on_u, n_on_u, ri_on_u from user u
```

`u.*` はカラム名のみで、別名はつかない。
テーブル結合した時にそれぞれのテーブルに同じ名前のカラムがあれば、Duplicate column nameでエラーとなる。

```scala
val sqlBuilder3 = select(u.*).from(User as u)
println(sqlBuilder3.sql.value)
// select u.user_id, u.name, u.role_id from user u
```

***

ここまでをふまえて、[サブクエリ(FROM)](#サブクエリfrom) で、メインのselectに`resultName`を指定した理由を説明する。

```scala
val t = SubQuery.syntax("t").include(u, r)
val result = withSQL {
  select(u.resultName.userId, u.resultName.name, r.resultName.roleId, r.resultName.name).from(
    select(u.result.userId, u.result.name, r.result.roleId, r.result.name)
      .from(User as u)
      .innerJoin(Role as r)
      .on(u.roleId, r.roleId) as t
  )
}.map(UserWithRole(_, u.resultName, r.resultName)).list().apply()
```

上記のコードから発行されるSQL全体は以下のようになる。

```sql
select ui_on_u, n_on_u, ri_on_r, n_on_r 
from (
 select 
  u.user_id as ui_on_u, 
  u.name as n_on_u, 
  r.role_id as ri_on_r, 
  r.name as n_on_r 
 from user u 
 inner join role r 
 on u.role_id = r.role_id
) t;
```

サブクエリ結果のカラム名は`ui_on_u`のように別名で返ってくるため、メインでのカラム名指定は別名で行わなければならない。
`result`だと、`u.user_id as ui_on_u`のように変換されるため、`Unknown column 'u.user_id' in 'field list'`のようなエラーが発生する。
よって、別名のみに変換される`resultName`を指定する必要がある。

## まとめ
ソースのコメントが一番の参考資料でした。