Arelのススメ -- Arelを使ってみよう

前回 の続きです。

今回はArel::Predications とArel::Nodes::InfixOperationより、比較的わかりやすいArelの実装と出力されるSQLをご紹介します。

比較演算

score = Exam.arel_table[:score]

Exam.where(score.eq(42)).to_sql
Exam.where(score.not_eq(42)).to_sql
Exam.where(score.lt(42)).to_sql
Exam.where(score.gt(42)).to_sql
Exam.where(score.lteq(42)).to_sql
Exam.where(score.gteq(42)).to_sql

SELECT "exams".* FROM "exams" WHERE "exams"."score" = 42
SELECT "exams".* FROM "exams" WHERE "exams"."score" != 42
SELECT "exams".* FROM "exams" WHERE "exams"."score" < 42             
SELECT "exams".* FROM "exams" WHERE "exams"."score" > 42
SELECT "exams".* FROM "exams" WHERE "exams"."score" <= 42
SELECT "exams".* FROM "exams" WHERE "exams"."score" >= 42 

論理演算

score = Exam.arel_table[:score]

Exam.where(score.gteq(60).and(score.lteq(90))).to_sql
Exam.where(score.lt(60).or(score.gteq(90))).to_sql
Exam.where(score.eq(60).not).to_sql
Exam.where(score.lt(60).or(score.gteq(90)).invert).to_sql

SELECT "exams".* FROM "exams" WHERE "exams"."score" >= 60 AND "exams"."score" <= 90
SELECT "exams".* FROM "exams" WHERE ("exams"."score" < 60 OR "exams"."score" >= 90)
SELECT "exams".* FROM "exams" WHERE NOT ("exams"."score" = 60)
SELECT "exams".* FROM "exams" WHERE NOT (("exams"."score" < 60 OR "exams"."score" >= 90))

範囲演算

score = Exam.arel_table[:score]

Exam.where(score.between((60..90))).to_sql
Exam.where(score.between((60..))).to_sql
Exam.where(score.in([70,80,90])).to_sql

comment = Exam.arel_table[:comment]

Exam.where(comment.matches('%OK%')).to_sql
Exam.where(comment.matches_regexp('OK')).to_sql

SELECT "exams".* FROM "exams" WHERE "exams"."score" BETWEEN 60 AND 90
SELECT "exams".* FROM "exams" WHERE "exams"."score" >= 60
SELECT "exams".* FROM "exams" WHERE "exams"."score" IN (70, 80, 90)

SELECT "exams".* FROM "exams" WHERE "exams"."comment" ILIKE '%OK%'
SELECT "exams".* FROM "exams" WHERE "exams"."comment" ~ 'OK'

CASE式

period = Exam.arel_table[:period]
period_name = period
    .when(1).then('first')
    .when(2).then('second')
    .when(3).then('third')
    .else('others')

Exam.where(period_name.eq('second')).to_sql

SELECT  "exams".* FROM "exams"
WHERE CASE "exams"."period"
        WHEN 1 THEN 'first'
        WHEN 2 THEN 'second'
        WHEN 3 THEN 'third'
        ELSE 'others'
        END = 'second'

算術演算

score = Exam.arel_table[:score]

Exam.select(
  score,
  (score + 10).as('plus'),
  (score - 10).as('minus'),
  (score * 10).as('times'),
  (score / 10).as('div'),
  (score ^ 10).as('power'),
  (score & 0x0f).as('and'),
  (score | 0xff).as('or'),
  (score << 1).as('left_shift'),
  (score >> 1).as('right_shift')
)

SELECT
  "exams"."score",
  ("exams"."score" + 10)  AS plus,
  ("exams"."score" - 10)  AS minus,
  "exams"."score" * 10    AS times,
  "exams"."score" / 10    AS div,
  ("exams"."score" ^ 10)  AS power,
  ("exams"."score" & 15)  AS bit_and,
  ("exams"."score" | 255) AS bit_or,
  ("exams"."score" << 1)  AS left_shift,
  ("exams"."score" >> 1)  AS right_shift
FROM "exams"

文字列でSQLを組み立てるよりキレイでRubyらしい実装になっていると思います。
特に生成するSQLには () が必要に応じて付加されるので、演算子の優先順を間違えてしまうバグも起こりにくいと思います。

次回はJOINや関数をArelで実装する例をご紹介する予定です

関連記事

Arelのススメ — Arelを使うメリット