ActiveRecord4でこんなSQLクエリどう書くの? Squeel編


2013年 11月 04日

ActiveRecord4でこんなSQLクエリどう書くの? Merge編 では、関連先のscopeを使い回すことができるmergeを紹介しました。
ActiveRecord4でこんなSQLクエリどう書くの? Arel編 では、安全にmergeができるscopeをArelを使って組み立てる例を紹介しました。

前回紹介したArelですが、複雑なクエリを組み立てようと思うとarel_tableという記述がいたる所に登場してしまい、処理がごちゃごちゃしてしまいました。
このごちゃごちゃ感を回避するために利用するのが、ActiveRecordの拡張であるsqueelです。

今回は、Arelを使った処理をsqueelで書き直してみます。
テーブルは、前々回紹介したもの を利用します。

商品(Product)テーブルの最終更新日が10日以内のデータを取得したい、といった比較演算を含んだ処理は、書きにくいものでした。
ActiveRecordで書くと、

Product.where('updated_at >= ?', 10.days.ago)

となり、このような処理をscopeに書いてしまうとmergeが行えないということを前回学びました。
安全にmergeできるようにするため、Arelを使って書くと、

Product.where(Product.arel_table[:updated_at].gteq 10.days.ago)

となります。
Arelで処理を記述すると、いたる所にarel_tableという記述がでてきてしまい、処理がごちゃごちゃした感じになってしまいます。

これをsqueelを使って書き直すと、

Product.where{ updated_at >= 10.days.ago }

となります。記述がシンプルでわかりやすいものになりましたね。
where後の括弧が ( ではなく { になっていることに注意してください。squeelは上記のように、blockで処理を記述していきます。

Squeel導入方法

squeelの導入はとても簡単です。

Gemfileに

gem 'squeel'

と記述し、bundle install。これだけで、railsプロジェクトでsqueelが利用できるようになります。

比較演算

比較演算はsqueelを使って書くと、以下のようになります。

Product.where{ updated_at > 10.days.ago }
Product.where{ updated_at >= 10.days.ago }
Product.where{ updated_at < 10.days.ago }
Product.where{ updated_at <= 10.days.ago }

[13] pry(main)> Product.where{ updated_at > 10.days.ago }
  Product Load (0.9ms)  SELECT `products`.* FROM `products`
  WHERE `products`.`updated_at` > '2013-10-19 15:14:24'

OR

商品が2000円か、または3000円か、みたいな処理を書きたい場合、Arelで書くと、

price = Product.arel_table[:price]
Product.where(price.eq(2000).or(price.eq(3000)))

となりました。これをsqueelで書き直すと、

Product.where{ (price == 2000) | (price == 3000) }

となります。
注意する点は、orは || ではなく | となることです。
また、(price == 2000) の括弧は忘れないように。

[26] pry(main)> Product.where{ (price == 2000) | (price == 3000) }
  Product Load (0.5ms)  SELECT `products`.* FROM `products`   
  WHERE ((`products`.`price` = 2000 OR `products`.`price` = 3000))

ちなみに、andを書きたい場合は && ではなく & となります。

like

likeはArelを使って書くと、

Product.where(Product.arel_table[:name].matches('%For%'))

となりました。相変わらずarel_tableがでてきてしまいますね。

squeelで書き直すと、

Product.where { name =~ "%For%" }

となります。

[32] pry(main)> Product.where { name =~ "%For%" }
  Product Load (0.5ms)  SELECT `products`.* FROM `products` 
  WHERE `products`.`name` LIKE '%For%'

=~ でlikeとかわかりにくい!と思う方は、

Product.where { name.like "%For%" }

という記述も利用できるので、こちらを利用してみてはいかがでしょうか。
どちらも生成されるSQLは同じです。

ちなみに、like A or like B のようなSQLを記述したい場合、or と like を組み合わせて

Product.where { (name.like "%For%") | (name.like "%Bar%") }

と書けますが、こういう処理は良く行うものなので、簡単に記述できるmatches_anyという関数が存在します。

[38] pry(main)> Product.where { name.matches_any ["%For%","%Bar%"] }
  Product Load (0.3ms)  SELECT `products`.* FROM `products` 
  WHERE ((`products`.`name` LIKE '%For%' OR `products`.`name` LIKE '%Bar%'))

like A and like B と書きたかったら、matches_all です。

[39] pry(main)> Product.where { name.matches_all ["%For%","%Bar%"] }
  Product Load (0.8ms)  SELECT `products`.* FROM `products` 
  WHERE ((`products`.`name` LIKE '%For%' AND `products`.`name` LIKE '%Bar%'))

left outer join

left outer joinはArelで組み立てると、記述量がかなり多くなってしまいました。
組み立てたいSQLが、

SELECT 
  `product_collection_items`.* 
FROM 
  `product_collection_items` 
LEFT OUTER JOIN 
  `products` 
ON 
  `products`.`id` = `product_collection_items`.`product_id`
;

の場合、Arelを使って書くと、

product = Product.arel_table
product_collection_item = ProductCollectionItem.arel_table
join_condition = product_collection_item
  .join(product, Arel::Nodes::OuterJoin)
  .on(product[:id].eq(product_collection_item[:product_id]))
  .join_sources
ProductCollectionItem.joins(join_condition)

となりました。
結構つらいものがありましたが、squeelを使うと以下のように簡単にjoinできます。

ProductCollectionItem.joins{ product.outer }

シンプルですね。

union

squeelでは書きなおせないです。残念!
Arelを使って、

union_sql = Product
  .where(price: 2000)
  .union(Product.where(price: 3000))
  .to_sql
Product.from("#{union_sql} products")

という処理を書き続ける他、今のところ手段はありません。
Arelを使っても、to_sqlがでてきてしまうので、微妙といったら微妙ですが。

前回も紹介しましたが、unionを使うとActiveRecord::RelationではなくArel::Nodes::Unionが返ってきてしまうため、squeelでどう頑張ろうが書き直せません。

UnionがActiveRecord::Relationを返さないのはおかしい、という話題は https://github.com/rails/rails/issues/939 でも取り上げられています。
皆さんも是非 +1 コメントを書いて、早くこの要望が取り込まれることを祈ってください。

サブクエリ

select
  items.*
from
  product_collection_items items
where
  product_id in
  (
   select
     p.id
   from
     products p
   )
;

のようなサブクエリは、Arelを使うと、

product = Product.arel_table
item = ProductCollectionItem.arel_table
sub_query = item[:product_id].in(product.project(product[:id]))
Product.where(sub_query)

と書けました。squeelを使うともっと単純になります。

ProductCollectionItem.where{ id.in(Product.select(:id)) }

[4] pry(main)> ProductCollectionItem.where{ id.in(Product.select(:id)) }
  ProductCollectionItem Load (0.5ms)  
SELECT `product_collection_items`.* FROM `product_collection_items` 
WHERE `product_collection_items`.`id` IN (SELECT `products`.`id` FROM `products`)

exists

最後はexists。書きたいクエリは以下のとおり。

select 
  items.* 
from 
  product_collection_items items 
where exists 
( 
  select 
    'X'
  from 
    products p
  where 
    p.id = items.product_id 
)
;

Arelで書くと、

product = Product.arel_table
product_collection_item = ProductCollectionItem.arel_table
condition = product
  .where(product[:id].eq(product_collection_item[:id]))
  .project("'X'")
  .exists
ProductCollectionItem.where(condition)

でした。
Squeelで書くと、以下のようになります。

ProductCollectionItem.where { 
  exists(Product.where(id: product_collection_items.product_id).select("'X'")) 
}

生成されるSQLは以下のとおりです。

SELECT `product_collection_items`.* FROM `product_collection_items` 
WHERE (exists(SELECT 'X' FROM `products` 
WHERE `products`.`id` = `product_collection_items`.`product_id`))

existsという関数があるのかと思うかもしれませんが、実はsqueelにexistsはありません。
生成されたSQLをよくみると、SELECTやWHEREは大文字表記なのに、existsは大文字になってませんよね。

この部分、squeelに存在しない関数が書かれると、文字列としてそのままSQLに変換します。
ということで、次のような記述も可能です。(もちろん動きませんが)

[81] pry(main)> Product.where { abcdefg(Product.all) }
  Product Load (1.2ms)  SELECT `products`.* FROM `products` 
  WHERE (abcdefg(SELECT `products`.* FROM `products`))

このテクニックを使ってexistsを実現しているわけです。


まとめ

Arelを使ってSQLを組み立てることで、安全にmergeができるSQLを組み立てることができました。
しかし、Arelはどうしても記述が冗長というか、ごちゃごちゃした感じになりがちでした。
この問題を解決するのが、Squeelと呼ばれるActiveRecord拡張のGemでした。

どんなクエリもSqueelでスマートに書き直せる、という訳ではありませんでしたが、多くはArelで直接書くよりも綺麗に処理を記述できました。
複雑なSQLクエリを書く必要に迫られたとき、squeel導入を検討してみてはいかがでしょうか。