Skinny-ORMでOUTER JOINとかGROUP BYとか #scalikeJDBC

Skinny-ORMを久しぶりに触っています。

なんか以前(多分1年以上前)に比べて格段に使いやすくなっている気がしていて、ちょっと本気出して使い倒してみようかと思っているところです。(^^;

hasManyやbelongsToを使ったFinderAPIはまだイマイチ思った通りに動いてくれなくて、もうちょい研究が必要かと思ってますが、QueryAPIに関しては概ね思い通りのSQLが生成できるようになってきたので、忘れないうちにその成果をまとめておきます。

(ScalikeJDBCはそれなりに使うのが難しいので、たまに触ると毎回ソース読んでる気がするんですよね。。。)

モデルの定義

まずSELECTした結果を格納するモデルクラスの定義方法は単純にcase classを定義するだけです。

こんな感じ。

case class Organization(
  id: Long,
  name: String,
  url: Option[String],
  createdAt: org.joda.time.DateTime,
  updatedAt: org.joda.time.DateTime
)

文字列や数値は単純に対応するプリミティブな型に対応付ければOK。
日付型はjava.util.Calendarやjava.sql.Timestampなどにもマップできるようだけど、扱いやすいのでJodaTimeがおすすめです。

TypeBinderを自分で定義すればEnum等の独自クラスをcase classのフィールドで使用することもできますが、それはまた気が向いたら書きます。(^^;

NOT NULLでない項目はOptionで括れば値がある場合だけマップしてくれます。(NOT NULLな項目をOptionで指定しても特に問題はありません。例えばpassword項目をOption[String]にしておいて、Json化する際にuser.copy(password=None)とする、みたいな使い方もありだと思います。)

モデルを作成したら次はそのモデルのCRUDを扱うためのCompanion Objectを作成します。

多分ミニマムな定義は以下です。

object Orgaization extends SkinnyCRUDMapper[Organization] {
  override lazy val tableName = "organizations"
  override lazy val defaultAlias = createAlias("o")

  override def extract(rs: WrappedResultSet, rn: ResultName[Organization]): Organization = {
    autoConstruct(rs, rn)
  }
}

ちなみに既存のテーブルからモデルを自動生成したい場合は、

をcloneして、DBの接続設定を変更して実行すれば自動生成されます。

上記の定義はほとんど自動生成されたコードそのままです。(デフォルトで生成されるクラス名は「Organizations」のように複数形(というかテーブル名をキャメルケース化した名前)になるので、それだけ単数形に直しました。)

このCompanion Objectを介して、

findBy
findById
findAllBy
countBy
createWithNamedValues
updateById
deleteById

等々のいたれりつくせりなメソッド群の実行ができ、それだけでもかなりな部分が間に合うのですが、ここではそれらの説明は行いません。

今回は複雑なJoinやGroup By等をQueryAPIを使用して行う方法に主眼を置きます。

ちなみにモデルの定義では必ずしも対応するテーブルの全カラムを定義する必要はありません。

createdAtやupdatedAtがRDB側で常にTriggerで設定され、アプリ側では表示のためにも使う必要がないのであれば、それらはモデルから外しても構いませんし、数十のカラムがあるようなテーブルに対して通常使うカラムが数個のようなケースで一つのテーブルに対して複数のモデル(とそのCompanion Object)を作成することも多分問題はないはずです。(実際にやろうかどうか迷っているところだったり。。。)

GROUP BY、OUTER JOINを含むSELECT文の生成(QueryDSL編)

例として以下の条件でのSELECT文の発行を考えます。

使用するテーブルはOrganizationとMemberの二つ
Organization has many Member の関係がある
外部結合して、Organizationのモデルと所属するMemberの数を取得
- 所属するMemberは0のことがあり得る
それぞれのテーブルにdelete_flgがあり、対象はそれがfalseのもののみ

このSQLをScalikeJDBCのQueryDSLを使って書くと以下のようになります。(下記は実際に動いたものを例に合わせて書き直したものです。)

val offset = 0
val limit = 10
val o = Organization.syntax("o")
val m = Member.syntax("m")
val list = withSQL {
  select(
    o.result.*,
    sqls.count(m.id)
  )
  .from(Organization as o)
    .leftJoin(Member as m)
    .on(o.id, m.organizationId)
  .where(
    sqls.eq(o.deleteFlg, false)
      .and
      .eq(m.deleteFlg, false)
  )
  .groupBy(o.id)
  .orderBy(o.id).desc
  .limit(limit)
  .offset(offset)
}.map { rs =>
  val org = Organization.extract(rs, o.resultName)
  val cnt = rs.int("count")
  (org, cnt)
}.list.apply

withSQLで括られたブロックでScalikeJDBCの用意したQueryDSLの文法を使用してSELECT文を組み立てています。
QueryDSLには一般的なSQLで使用される構文がほぼすべて用意されているので、直接SQLを書くのと同じような感覚で、かつフィールド名等のリテラルはコンパイル時にチェックしながら書くことができます。

この例でのポイントはselectの引数にo.result.*を与えていることです。
これによりResultSetからのmap時にOrganization#extractメソッドを使用してモデルを構築することができています。
(o.resultやo.resultNameが何かということについては後述します。)

それなりに読みやすいですがSQLを読み慣れている身からすれば、whereの条件指定のあたりを読み解くのに脳内変換が必要な感じです。

GROUP BY、OUTER JOINを含むSELECT文の生成(生SQL編)

同じ内容をsql補間子を使って書き直したものが以下です。

val offset = 0
val limit = 10
val o = Organization.syntax("o")
val m = Member.syntax("m")
val list = sql"""
  SELECT
    ${o.result.*},
    count(m.id) as count
  FROM organizations c
  LEFT OUTER JOIN members m ON (o.id = m.organization_id)
  WHERE o.delete_flg = false
    AND m.delete_flg = false
  GROUP BY o.id
  ORDER BY o.id desc
  LIMIT ${limit} OFFSET ${offset}
""".map { rs =>
  val org = Organization.extract(rs, o.resultName)
  val cnt = rs.int("count")
  (org, cnt)
}.list.apply

こちらはほとんど生SQLそのままです。

それでいてSELECT句に${o.result.*}を渡せたりするので、mapの部分はQueryDSLを使用した場合とまったく同じようにOrganization#extractが使えます。

QueryDSLと生SQLのどちらを使うかは意見の分かれるところでしょうが、QueryDSLを使用しても結局のところ脳内でどういうSQLが生成されるかを脳内変換しながら書いているので、個人的には生SQLの方が好みです。

カラム名はどこから取得するべきか？

先のQueryDSLでは

o.result.*
o.id
o.resultName

など何箇所か別の方法でカラム名を扱っています。
これらはどのように使い分ければ良いのでしょうか？

おそらくここがSkinny-ORM/ScalikeJDBCのもっともわかりにくいところかと思いますが、Skinnyでカラム名を扱う書式はよく見かけるものだけでも次のようなものがあります。

Organization.column.id
Organization.defaultAlias.id
Organization.defaultAlias.result.id
Organization.defaultAlias.resultName.id
Organization.syntax("o").id
Organization.syntax("o").result.id
Organization.syntax("o").resultName.id

defaultAliasはcreateAliasメソッドで作成されたAliasですがこれは、内部的にsyntaxメソッドを呼び出しているので、実質的にはdefaultAliasとsyntaxは同じです。

これらの違いを知るためには実際に値を書き出してみるのが一番はやいと思います。

val o = Organization.syntax("o")
println("Organization.column.id: " + Organization.column.id)
println("o.id: " + o.id)
println("o.result.id: " + o.result.id)
println("o.resultName.id: " + o.resultName.id)

結果は以下のように出力されます。

Organization.column.id: SQLSyntax(value: id, parameters: List())
o.id: SQLSyntax(value: o.id, parameters: List())
o.result.id: SQLSyntax(value: o.id as i_on_o, parameters: List())
o.resultName.id: SQLSyntax(value: i_on_o, parameters: List())

いずれの結果もSQLSyntaxオブジェクトを返していますが、中身のvalueが少しずつ異なります。

並べてみればなんとなく違いがわかると思いますが、これらはそれぞれ

Organization.column.id -> 修飾なしのカラム名
o.id -> テーブルエイリアスを付加したカラム名
o.result.id -> テーブルエイリアスを付加したカラム名にさらにユニークな列エイリアスを付加した文字列
o.resultName.id -> o.resultで使用される列エイリアス部分

を返します。

ちなみにいずれの方法でも「*」が使用することができ、
o.result.*とすれば、すべての列が列エイリアスを付加した形でカンマ区切りで結合されて返されます。

ここで少し戻って、先のQueryDSL/生SQLの例でOrganization#extractが何故使用できるかを説明すると、

クエリのSELECT句にo.result.*が含まれており、
extractは第一引数のWrappedResultSetから、第二引数で渡された名前を使用してオブジェクトを生成するメソッド

だからです。

わかってしまえばとても単純で、SELECT文中のどこで何を使えば良いかも自明かと思います。

SQLSyntaxとは何か？

最後にSQLSyntaxとは何なのかについて軽く説明しておきます。

考え方としては、

StringBuilderにいくつかのクエリ生成用のメソッドが追加されたもの

と思えば良いと思います。

sqls.orderBy(o.id).desc

は

sqls.append(sqls"ORDER BY ").append(o.id).append(sqls" desc")

と同じです。(appendの引数にsqls補間子が必要なのはSQL Injectionを避けるためです。)

他にも各メソッド呼び出しで渡されたパラメータがPreparedStatementのパラメータになるなどの機能もありますが、ここで重要なのは各メソッド呼び出しが単純に前に生成された文字列の後ろに文字列を足しているだけという点です。

なので、

where.and.and

とか

orderBy(o.id).where

とかSQLとして不正な順序でメソッド呼び出しを行うとあっさりと不正SQLを生成します。(コンパイルエラーにはなりません。)

逆に言うと、QueryDSLを使いつつもwhereの部分だけ

where(sqls"o.delete_flg = false and m.delete_flg = false")

のように生SQLを書けたりもします。

ここまでわかれば、あとは自由自在に自分の思った通りのSQLを生成できるんじゃないですかね？(^^;;;
findByなどで条件を指定する際にもこれらの知識は役に立つはずです。