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

2013年 10月 29日

ActiveRecord4でこんなSQLクエリどう書くの？ Merge編では、関連先のscopeを使うことができるmergeを紹介しました。mergeを使う事で、変更に強いクエリを美しく組み立てることができました。

今回は、ActiveRecord4で書きにくいSQLクエリたちを紹介します。
書きにくいクエリとは、具体的には以下のものがあげられます。

比較演算
OR
like
left outer join
union
サブクエリ
exists

ActiveRecordでこれらのクエリを組み立てる場合、どうしても美しくない（SQL文字列をべったり書いてしまうような）書き方となってしまいがちです。
この処理を美しく書くためには、ActiveRecordだけの機能では足りず、ActiveRecordが内部で使っているArelというライブラリを使う必要がでてきます。

今回は、Arelを使って華麗にSQLクエリを組み立てる方法を紹介します。

ActiveRecordで書きにくいクエリとは、いったいどういうものでしょうか。

比較演算を使ったクエリは書きにくいものの部類に入ります。
商品(Product)テーブルの最終更新日が10日以内のデータを取得したい、と言った場合、SQLでは、

select
  *
from
  products p
where
  p.updated_at > '2013-10-17 00:00:00'  -- 今日が2013-10-27として

とかけます。これをActiveRecordで書くと、

Product.where(‘updated_at > ?’, 10.days.ago)

となります。文字列べったり書いてしまっている感じがイケてないですね。
イケてないだけではなく、こういう書き方をしてしまうと以下の問題が生じます。

mergeができない（場合がある)
DBを変えるとうまく動かない(場合がある)

大文字小文字の違い
そもそもクエリが違う(mysql/postgres特有のlimit, offsetは他のDBには無い等)

完全に文字列で組み立ててしまうと、default_scopeが効かない
美しくない

そう、一番の問題は前回紹介したmergeが使えなくなることです。
例として、次のようなscopeを定義してみました。

class Product < ActiveRecord::Base
  scope :available, -> do
    where('updated_at > ?', 10.days.ago) 
  end
end

もうダメですね。これmergeできないです。
前回紹介したテーブルを使って、次のような処理を書いてみます。

[14] pry(main)> ProductCollection.joins(:products).merge(Product.available)
  ProductCollection Load (1.3ms)  
SELECT `product_collections`.* FROM `product_collections` 
INNER JOIN `product_collection_items` 
ON `product_collection_items`.`product_collection_id` = `product_collections`.`id` 
INNER JOIN `products` 
ON `products`.`id` = `product_collection_items`.`product_id` 
WHERE (updated_at > '2013-10-16 16:39:36')

Mysql2::Error: Column 'updated_at' in where clause is ambiguous: (以下略)

updated_at のカラムの指定があいまいだ！とmysqlに怒られてしまいました。

文字列で組み立てるとscope関連がうまく動かなくなってしまったり、DB変えると動かなくなってしまったりと問題は沢山あります。Arelを使ってクエリを組み立てておけば、こういった問題が生じることはありません。

class Product < ActiveRecord::Base
  scope :available, -> do
    where(arel_table[:updated_at].gt 10.days.ago) 
  end
end

というようにArelを使って書いておけば、mergeしたときに

WHERE (`products`.`updated_at` > '2013-10-16 16:39:36')

というSQLクエリを組み立ててくれます。

Arelを使ってクエリを組み立てる必要性はわかっていただけたかと思います。
では、書きにくいクエリたちをArelを使って組み立てる方法を紹介していきます。

比較演算

< (less than), <= (less than equal), >(greater than) , >= (greater than equal) を使った条件は、Arelを使うと以下のように書けます。

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

上記処理の、

Product.arel_table[:updated_at].gt 10.days.ago

という部分をSQLとして出力してみると、

[36] pry(main)> Product.arel_table[:updated_at].gt(10.days.ago).to_sql
=> "`products`.`updated_at` > '2013-10-16 17:10:56'"

となります。これをwhere句に書いているので、うまく処理が行えているわけですね。

OR

or条件もActiveRecordでは書きにくいものです。
商品が2000円か、または3000円か、みたいな処理を書きたい場合は、何も考えずに書くと

[44] pry(main)> Product.where('price = ? or price = ?', 2000, 3000)
  Product Load (0.6ms)  SELECT `products`.* FROM `products` WHERE (price = 2000 or price = 3000)

みたいになってしまいます。
Arelで書き直すと以下のようになります。

[69] pry(main)> price = Product.arel_table[:price]
[70] pry(main)> Product.where(price.eq(2000).or(price.eq(3000)))
  Product Load (0.9ms)  SELECT `products`.* FROM `products` 
  WHERE ((`products`.`price` = 2000 OR `products`.`price` = 3000))

like

likeも書きにくいクエリです。

likeくらい簡単に書けるようにしとけよと思わなくもないですが、どうもレーシング大好きな某railsエンジニアさんはlikeなんていらないと思っているようです。（この話どこで聞いたのか思い出せないですが、そういう場合は直接SQLかけよとかそんなことを言ってたような気がします）

arelではmatchesを使う事でlikeクエリが書けます。

[73] pry(main)> Product.where(Product.arel_table[:name].matches('%For%'))
  Product Load (1.1ms)  SELECT `products`.* FROM `products` 
  WHERE `products`.`name` LIKE '%For%'

left outer join

ここら辺から、だんだんArelで組み立てるのが苦しくなってきます。
outer joinは文字列で書くと次のようになります。

[12] pry(main)> ProductCollectionItem
.joins(‘left outer join products on products.id = product_collection_items.product_id’)

  ProductCollectionItem Load (0.3ms)  
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)

[5] pry(main)> ProductCollectionItem.joins(join_condition)
  ProductCollectionItem Load (0.5ms)  
SELECT `product_collection_items`.* FROM `product_collection_items` 
LEFT OUTER JOIN `products` 
ON `products`.`id` = `product_collection_items`.`product_id`

Arelで組み立てるという目標は達成できましたが、かなり苦しい感じですね。

union

unionもArelを使って書いてみましょう。例えば以下のようなSQLを組み立てたいとします。
（こんなんunionする必要なくね？とお思いでしょうが、サンプルなので勘弁してください）

select 
  * 
from 
(
  select
    p1.*
  from
    products p1
  where 
    p1.price = 2000 
  union
  select 
    p2.* 
  from 
    products p2
  where 
    p2.price = 3000
) products
;

Arelで書くとこんな感じでしょうか。

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

あれ、Arel使ってなくね、と思うかもしれませんが、なんと

Product.where(price: 2000).union(Product.where(price: 3000))

の結果が、Arel::Nodes::Unionになってしまいます（これは仕様おかしくないか？と常々思っております）。

しかも、Arel::Nodes::Unionではデータを取得できないため、to_sqlしてfromにそのSQLを書くという、なんだか分けのわからない状態となっております。もうかなり無理がありますね。

ちなみにunion allは以下のように書きます。

union_sql = Arel::Nodes::UnionAll.new(
  Product.where(price: 2000).ast, 
  Product.where(price: 3000).ast).to_sql
Product.from("#{union_sql} products")

サブクエリ

次はサブクエリ。以下のようなSQLを組み立てたい。

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)

[117] pry(main)> ProductCollectionItem.where(sub_query)
  ProductCollectionItem Load (0.9ms)  SELECT `product_collection_items`.* 
FROM `product_collection_items` 
WHERE `product_collection_items`.`product_id` IN 
(SELECT `products`.`id` FROM `products`)

まあ目標は達成できてます。

exists

最後はexists。業務アプリじゃよく使いますよね。
次のようなSQLを書きたいとしましょう。

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)

結構きついですね。。。結果も書いておきます。

[97] pry(main)> ProductCollectionItem.where(condition)
  ProductCollectionItem Load (0.9ms)  
SELECT `product_collection_items`.* FROM `product_collection_items` 
WHERE (EXISTS (
SELECT 'X' FROM `products` 
WHERE `products`.`id` = `product_collection_items`.`id`))

ちなみに、not existsにしたかったら、conditionを以下のように書き換えます。

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

おまけ: ActiveRecord3で書きにくかったクエリたち

ActiveRecord3では書きにくかったクエリたちも一応紹介しておきます。
以下のものはActiveRecord4では改良されています。

order
where not

Productのidでorder ascしたい場合は

Product.order(:id)

で可能です。しかし、ActiveRecord3の場合、order descが簡単にはできなかったのです。

Product.order('id desc') -- ActiveRecord3の場合

4ではさすがにこの点は改良されています。

Product.order(id: :desc)

where notも同様に文字列で書かないといけないクエリでした。

Product.where('not id = ?', 3) -- ActiveRecord3まで

ActiveRecord4では次のように書きます。

Product.where.not(id: 3)

まとめ

文字列でSQL組み立ててしまうと、mergeができなくなったり、DB変えたら動かなくなったり、default_scopeが動かくなってしまったりと、問題は山ほどあります。これらを回避するためには、SQLをArelで組み立てれば良いという事を学びました。

でも、ちょっと複雑なことをしようと思えばArelでクエリ組み立てるのがかなりきつくなってきます。left outer joinやexistなんか特にきつかったですね。なんだかごちゃごちゃした処理になってしまいます。

このごちゃごちゃ感を回避する、うまい方法なないものでしょうか。
そんな時に利用するのが、ActiveRecord拡張であるsqueel です。これを使うと、とてもスマートにSQLを組み立てられるようになります！

次回はsqueelを使って、華麗にSQLを組み立てる例を紹介します。

< 前の記事へ次の記事へ >

2024年1月23日

社内イベント★ウォーキング大会 2023秋★

#人事ブログ