Arelで色んなSQLを組み立ててみる

(この記事は Ruby Advent Calendar jp:2010の 15 日目です。前日は tomohiro68 さんでした。)

Arelとは

Arelの概要については@a_matsudaさんのgihyoの記事を参照してください。
http://gihyo.jp/dev/serial/01/ruby/0043

はじめに

扱うRDBはSQlite3です。
例として以下のようなスキーマを持ったテーブルを取り扱い、進めていきます。

class CreateBooks < ActiveRecord::Migration
  def self.up
    create_table :books do |t|
      t.string :name
      t.string :category

      t.timestamps
    end
  end

  def self.down
    drop_table :books
  end
end

基本的な操作

where句
books = Arel::Table.new :books

#=
books.project(Arel.sql('*')).where(books[:id].eq(1)).to_sql
   => SELECT * FROM "books" WHERE "books"."id" = 1

#like
books.project(Arel.sql('*')).where(books[:name].matches('book_name_2_%')).to_sql
   => SELECT * FROM "books" WHERE "books"."name" LIKE 'book_name_2_%'

#>,<,andで条件を追加
books.project(Arel.sql('*')).where(books[:id].gt(5)).where(books[:id].lt(10)).to_sql
   => SELECT * FROM "books" WHERE "books"."id" > 5 AND "books"."id" < 10

#orで条件を追加
books.project(Arel.sql('*')).where(books[:id].gt(5).or(books[:id].lt(10))).to_sql
   => SELECT * FROM "books" WHERE ("books"."id" > 5 OR "books"."id" < 10)
order by句
books = Arel::Table.new :books

#default
books.project(Arel.sql('*')).order(books[:id]).to_sql
   => SELECT * FROM "books" ORDER BY "books"."id"

#昇順
books.project(Arel.sql('*')).order(books[:id].asc).to_sql
   => SELECT * FROM "books" ORDER BY "books"."id" ASC

#降順
books.project(Arel.sql('*')).order(books[:id].desc).to_sql
   => SELECT * FROM "books" ORDER BY "books"."id" DESC
集計関数とgroup by
books = Arel::Table.new :books

#count
books.project(books[:id].count, books[:category]).group(books[:category]).to_sql
   => SELECT COUNT("books"."id"), "books"."category" FROM "books" GROUP BY "books"."category"

#集計カラムに別名をつける
books.project(books[:id].count.as('count_id'), books[:category]).group(books[:category]).to_sql
   => SELECT COUNT("books"."id") AS count_id, "books"."category" FROM "books" GROUP BY "books"."category"

#sum
books.project(books[:id].sum.as('sum_id'), books[:category]).group(books[:category]).to_sql
   => SELECT SUM("books"."id") AS sum_id, "books"."category" FROM "books" GROUP BY "books"."category"

おまけ

SQLで既存のテーブルから値を参照する方法がありますが、
一時テーブル(?)を作ってそこから参照することもできます。

select
  1 as num
=>
num 
----
1 

unionでつなげると

select
  *
from (
  select
    1 as num
  union
  select
    2 as num
) as tmp
=>
num 
----
1   
2 

これをArelで書くと以下の様になります(使い方違っているような気もしますが...

table = Arel::Table.new nil
table.from('(select 1 as num union select 2 as num) as tmp').project(Arel.sql('*')).to_sql
   => SELECT * FROM (select 1 as num union select 2 as num) as tmp

無理矢理ですね。

おまけをjoin句でinner joinしてみる!

さらにおまけで紹介したものを未紹介だったjoin句でつないでみます。

table = Arel::Table.new nil
books = Arel::Table.new :books
table = table.from('(select 1 as num union select 2 as num) as tmp').project(Arel.sql('*'))
table.join(books).on(books[:id].eq(Arel.sql('tmp.num'))).to_sql
   => SELECT * FROM (select 1 as num union select 2 as num) as tmp INNER JOIN "books" ON "books"."id" = tmp.num

もはやfind_by_sqlを使え状態です。
しかも使う機会があるのかさえ微妙です。

わかってないこと

サブクエリや型変換を伴うようなSQLの組み上げについては調査中です。
型変換についてはfind_by_sqlを使わないと無理な気がしています。
サブクエリはSQLを一つにまとめる方法でなければできたのですが。

まとめ

このようにArelを使うことによって、より自然な形でSQLを組み立てることができるようになりました。
またArelになってからSQL厨の皆様にとっても使いやすくなったのではないかと思います。