Arel::Nodes を使って Arel で複雑な SQL文を作っちゃおう
だいぶArelで試行錯誤した。
だいぶ書き方を把握したので、メモ。
（試行環境）
Ruby 2.2.2p95
Rails 4.2.1
MySQL 5.5
（参考）
Arel でサブクエリ - Qiita - Ruby on Rails Advent Calendar 2012 / Sep 8日目
RailsのArelのTips - Qiita
ActiveRecordのarel_tableから作れる条件式まとめ - Qiita
基本
arel_table
Arel はSQL文の各句の構成要素を作成するためのクラス群と考えて良い。
例えば、あるモデル Product があった場合、Product.arel_table をもとに、AcriveRecordのメソッドと組み合わせて用いる。
Product
.select( :id )
.where( Product.arel_table[:price].gteq(1000) )
# => "SELECT `products`.`id` FROM `products` WHERE `products`.`price` >= '1000'"
Arel
Arelクラス直下では、２つクラスメソッドが定義されている。
Product.select( Arel.star )
Product.select( Arel.sql('*') )
Arel.sql クラスメソッド
Arel::Nodes::SqlLiteral.new のラッパメソッド。生のSQL文をクラス化する。
Arel.star クラスメソッド
Arel.sql('*') と等価。SQL文 * を表す。
Arel::Nodes
ActiveRecordに用意されていないSQL文を書く場合はArel::Nodesのサブクラスを組み合わせていくことになる。
わりと利用頻度が高そうなサブクラスとクラスメソッドについて解説する。
クラスメソッド
Arel::Nodes#build_quoted
Arel::Nodes::NamedFunctionなどへ、引数として実数（Float）や文字列、nilなどを渡す場合、このメソッドでくくる。さもなくば、エラーになる。
ただし、整数(Integer)はそのままで大丈夫なようだ。
# Error cases
p = Product.arel_table
Product.select( Arel::Nodes::NamedFunction.new('IFNULL', [ p[:tax_rate], 0.08]) )
# => RuntimeError: unsupported: Float
Product.select( Arel::Nodes::NamedFunction.new('IFNULL', [ p[:tax_rate], 'FREE']) )
# => RuntimeError: unsupported: String
Product.select( Arel::Nodes::NamedFunction.new('IF', [ p[:tax_rate].eq(''), nil, 0.08]) )
# => RuntimeError: unsupported: NilClass
# No error cases
p = Product.arel_table
Product.select( Arel::Nodes::NamedFunction.new('IFNULL', [ p[:tax_rate], Arel::Nodes.build_quoted(0.08)]) )
Product.select( Arel::Nodes::NamedFunction.new('IFNULL', [ p[:tax_rate], Arel::Nodes.build_quoted('FREE')]) )
Product.select( Arel::Nodes::NamedFunction.new('IF', [ p[:tax_rate].eq(''), Arel::Nodes.build_quoted(nil), Arel::Nodes.build_quoted(0.08)]) )
サブクラス
Arel::Nodes::NamedFunction
任意のSQL関数の呼び出しを表現するために使う。
例えば、IFやIFNULL、CAST、STD_POPやVAR_POPなど、Rails側で用意していないSQL関数などはこのArel::Nodes::NamedFunctionを使って呼び出せる。
（初期化時の）第一引数に関数名、第二引数にその関数への引数の配列、第三引数はオプションで（カラムとして使う場合の）エイリアス名を与える。
# Usage:
# Arel::Nodes::NamedFunction.new( String func_name, Array args [,String Alias] )
p = Product.arel_table
Product.select( Arel::Nodes::NamedFunction.new('IFNULL', [ p[:price_taxed], 0], 'intax' ) )
# => "SELECT IFNULL(`products`.`price_taxed`, 0) AS intax FROM `products`
Arel::Nodes::Grouping
括弧()で囲む。
四則計算等で括弧を使いたい場合などに重宝する。次のArel::Nodes::InfixOperation のサンプル参照。
# Usage:
# Arel::Nodes::Grouping( object )
Arel::Nodes::InfixOperation
四則演算を行う場合に使う。
（初期化時の）第二引数と第三引数を、第一引数で演算した内容を形成する。
入れ子のように使うこともできる。
# Usage:
# Arel::Nodes::InfixOperation.new( String operation, left_object, right_object )
p = Product.arel_table
Product.select( Arel::Nodes::InfixOperation.new('/', p[:price_taxed], Arel::Nodes.build_quoted(1.08)).as("extax") )
# => "SELECT `products`.`price_taxed` / 1.08 AS extax FROM `products`
t = Trapezoid.arel_table
ubase, lbase, height = t[:upper_base], t[:lower_base], t[:height]
Trapezoid.select(
Arel::Nodes::InfixOperation.new( '/',
Arel::Nodes::InfixOperation.new( '*',
Arel::Nodes::Grouping.new(
Arel::Nodes::InfixOperation.new( '+', ubase, lbase ),
),
height
),
2
).as("area")
)
# => SELECT (`trapezoids`.`upper_base` + `trapezoids`.`lower_base`) * `trapezoids`.`height` / 2 AS area FROM `trapezoids`
Arel::Nodes::SqlLiteral
任意のSQL文字列を表す。
例えば、CAST()関数に使われるようなDECIMALやCHARなどの構文中に必要な文字列は、直接Stringで表さずにこのクラスでラッピングする。次のArel::Nodes::Asのサンプル（samnple2）を参照。
# Usage:
# Arel::Nodes::SqlLiteral.new( String raw_sql )
Arel::Nodes::As
AS による別名定義の文 x AS y を作る。
SELECT句やWHERE句で使われるASや、CAST()関数などで使われるASも作れたり、汎用的に使える。
前者で使う場合は、.asを使った方が簡単（Arel::Nodes::Asのラッパーメソッドとなっている）。
（初期化時の）第一引数、第二引数に当該オブジェクトを渡す。第２引数が文字列の場合はbuild_quotedする。
# .as
p = Product.arel_table
Product.select( p[:id].as('no') ) # => "SELECT `products`.`id` AS no ...
# Usage:
# Arel::Nodes::As( left_object, right_object )
p = Product.arel_table
Product.select(
Arel::Nodes::NamedFunction.new(
'CAST', [ Arel::Nodes::As.new(p[:code], Arel::Nodes::SqlLiteral.new('DECIMAL')) ] # = Arel.sql('DECIMAL')
)
)
# => SELECT CAST(`products`.`code` AS DECIMAL) FROM `products`
Arel::Nodes::OuterJoin
通常ActiveRecordで.joinsをすると、INNER JOINとなる。
LEFT JOIN を使いたい場合は、Arelを使ってJOIN句部分のSQL文を作成するとよい（.join_sources）。
このJOIN句の作成時に、OUTER JOINであることを指し示す定数的なクラスとして用いられるのがArel::Nodes::OuterJoinで、下記サンプルのように使う。
p = Product.arel_table
m = Maker.arel_table
p.select( p[:id], p[:name], m[:name].as('maker_name') )
.joins(
p.join( m, Arel::Nodes::OuterJoin )
.on( p[:maker_id].eq( m[:id] ) )
.join_sources
)
応用
Modelのソース中にいちいち Arel::Nodes::NamedFunction などを書き綴っていっても可読性も保守性も悪いので、concernsディレクトリの下に構文作成用クラスとして作ったりするとよいだろう。
def highprice_products
Product
.select( :name, :price )
.select( ColumnRelPrice.new(Product).intax.as("price_intax") )
.where( Product.arel_table[:price].gteq(100_000) )
.order( price: :desc )
end
class ColumnRelPrice
def initialize(t)
@table = t.arel_table
end
def intax
Arel::Nodes::InfixOperation.new(
'*', @table[:price], @table[:consumption_tax_rate]
)
end
end
（おわり）