k4200’s notes and thoughts

Programmer side of k4200

色んなSQLをSquerylで書くと?

Squryl で複雑なSQLを実行させたい

ORMって、ちょっと複雑なSQLを実行させようとすると面倒な事が多くて、みんな仕方なしに使ってること多くない?

という主観はさておき、Squrylの場合もドキュメントサンプルプログラムに載っている例以外の事をやろうとすると、ぐぐってMLのアーカイブの中から答えを探してこなきゃいけないって事が多いと思う。

今回は、複雑(と言っても大したことないんだけど・・・)なSQLとそれをどうSquerylで書くかをまとめてみた。

方針等

方針として、SQLを作って、それをSqurylで表現する。論理的に等価な別のSQLに書き直すってのは無しで。論理的に等価でも、DBMSの最適化で別の実行計画になる場合もあるので。

本記事で書くコードは、実際使っているコードからコピペしてテーブル名とかを書き換えたりしてるので、コンパイルが通らなかったりするかもしれないので、その際はご容赦を。

LEFT JOIN + IS NULL

こんなの。

SELECT *
FROM t1
LEFT JOIN t2 on (t1.id = t2.id)
WHERE t2.id IS NULL

Squeryl だとこう。

join(t1, t2.leftOuter)((t1, t2) =>
  where(t2.map(_.id) isNull) // 直感的じゃないよね
  select(t1)
  on(t1.id === t2.map(_.id))
)

参考にしたの

相関サブクエリー

小クエリーから親クエリーのテーブルを参照する、と。

SELECT *
FROM t1
WHERE EXISTS (
      SELECT id
        FROM t2
       WHERE t1.id = t2.id)

Squerylだと・・・

from(t1)((t1) =>
  where(exists(from(t2)((t2) =>
              where(t2.id === t1.id)
              select(t2.id)))
  select(t1)
  on(t1.id === t2.map(_.id))
)

まぁ割と直感的。

複数のカラムでgroup by

ドキュメントにも少し書いてあるけど、まぁ一応備忘録的にも。

SELECT t1.col1, t1.col2, count(*)
FROM t1
GROUP BY col1, col2
from(t1)((t1) =>
  groupBy(t1.col1, t1.col2)
  compute(count())
)

まとめ

ORM って利点もわかるけど、面倒な事が多い気もする。

有名なORMだったら逆引きxxみたいなのありそうだけど、Squerylだとなさそうだし、今後も気が向いたら追記予定。