ActiveRecord4でこんなSQLクエリどう書くの？ Merge編

2013年 10月 24日

「このデータ取得するのにSQLではこういう風に書けばいいんだけど、ActiveRecordでは一体どう書けばいいの？」

毎回この課題に悩まされています。

特に業務アプリの場合、とてつもなく複雑なSQLを投げる場合があります。
ものすごい数のテーブルをjoinして、existsで条件みて、union allして。。。なんていう処理がでてくると、さすがにActiveRecordやDatamapperを使ってクエリを組み立てるのをあきらめて、直接SQLを書いてしまうことがあります。

でも、できればActiveRecordを使ってスマートにSQLを組み立てたいものです。
scopeで書いておけば、処理も使い回せますしね。

ということで、こういうSQLはActiveRecordではこういう風に書けばできますよ！っていうサンプルを色々かいてみることにしました。

第一弾は、たぶんActiveRecordで最も便利な機能、mergeを使ったサンプルを紹介します。
これが使えると処理が綺麗にかけるし、使い回せるようにもなるし、ActiveRecordすごい！って思えるようになります。

今回はショッピングサイトを作っているという想定で、以下のテーブルを用意してみました。

商品(Products) テーブルは、何か商品が入っています。今回は書籍が入っているとしましょう。
商品コレクション(ProductCollections) と商品コレクションアイテム(ProductCollectionItems)は、商品の特集ページなどに使う商品一覧を保存しておくためのテーブルとしましょう。

上記テーブルのmodelも作っておきます。

まずは、商品を表すProducts。

# == Schema Information
#
# Table name: products
#
#  id          :integer          not null, primary key
#  name        :string(255)
#  price       :integer
#  open_status :integer
#  created_at  :datetime
#  updated_at  :datetime
#

class Product < ActiveRecord::Base
end

次は商品コレクションを表すProductCollections。

# == Schema Information
#
# Table name: product_collections
#
#  id         :integer          not null, primary key
#  title      :string(255)
#  created_at :datetime
#  updated_at :datetime
#

class ProductCollection < ActiveRecord::Base
  has_many :product_collection_items
  has_many :products, through: :product_collection_items
end

最後はコレクション内の商品一覧を表すProductCollectionItems。

# == Schema Information
#
# Table name: product_collection_items
#
#  id                    :integer          not null, primary key
#  product_collection_id :integer
#  product_id            :integer
#  created_at            :datetime
#  updated_at            :datetime
#

class ProductCollectionItem < ActiveRecord::Base
  belongs_to :product_collection
  belongs_to :product
end

なんてことはない、普通のmodelです。
データも次のものを用意しておきました。

mysql> select * from products;
+----+---------------------+-------+-------------+---------------------+---------------------+
| id | name                | price | open_status | created_at          | updated_at          |
+----+---------------------+-------+-------------+---------------------+---------------------+
|  1 | Ruby For Beginners  |  1200 |           1 | 2013-10-21 15:55:02 | 2013-10-21 15:55:02 |
|  2 | Rails For Beginners |  2000 |           1 | 2013-10-21 15:55:18 | 2013-10-21 15:55:18 |
|  3 | Unknown             |  3000 |           0 | 2013-10-21 15:55:38 | 2013-10-21 15:55:38 |
+----+---------------------+-------+-------------+---------------------+---------------------+
3 rows in set (0.00 sec)


mysql> select * from product_collections;
+----+--------------------+---------------------+---------------------+
| id | title              | created_at          | updated_at          |
+----+--------------------+---------------------+---------------------+
|  1 | Ruby Collection    | 2013-10-21 15:55:51 | 2013-10-21 15:55:51 |
|  2 | Rails Collection   | 2013-10-21 16:12:30 | 2013-10-21 16:12:30 |
|  3 | Unknown Collection | 2013-10-21 16:14:51 | 2013-10-21 16:14:51 |
+----+--------------------+---------------------+---------------------+
3 rows in set (0.00 sec)

mysql> select * from product_collection_items;
+----+-----------------------+------------+---------------------+---------------------+
| id | product_collection_id | product_id | created_at          | updated_at          |
+----+-----------------------+------------+---------------------+---------------------+
|  1 |                     1 |          1 | 2013-10-21 15:55:55 | 2013-10-21 16:03:08 |
|  2 |                     1 |          2 | 2013-10-21 15:55:58 | 2013-10-21 15:55:58 |
|  3 |                     1 |          3 | 2013-10-21 15:56:01 | 2013-10-21 15:56:01 |
|  4 |                     2 |          2 | 2013-10-21 16:14:26 | 2013-10-21 16:14:26 |
|  5 |                     3 |          3 | 2013-10-21 16:17:20 | 2013-10-21 16:17:20 |
+----+-----------------------+------------+---------------------+---------------------+
5 rows in set (0.00 sec)

文字でみるとわかりづらいので、イメージ図も描いときます。
こんな感じでしょうか。

で、ここからが本題。

「有効な商品を含んでいる商品コレクション(ProductCollection)のID一覧を取得したい！」
という処理を書きたくなった場合、どうやってデータを取得しますか？

「有効な商品」の条件は、まずはProductsのopen statusが1の場合、としましょう。
SQLでは次のように書けるかと思います。

select distinct
  pc.id
from
  product_collections pc
inner join
  product_collection_items items
on
  pc.id = items.product_collection_id
inner join
  products p
on
  items.product_id = p.id
where
  p.open_status = 1
;

いやいやもっと効率的なSQLあるじゃん！とかそういう話はおいといて、今回は上記クエリをActiveRecordで書いてみますか。

まずは、product_collections に product_collection_items と products を joinするところまで。以下のようなクエリが取得できれば成功のはず。

select
  pc.id
from
  product_collections pc
inner join
  product_collection_items items
on
  pc.id = items.product_collection_id
inner join
  products p
on
  items.product_id = p.id
;

ActiveRecordで書くとこんな感じ？

ProductCollection.joins(:product_collection_items).joins(:products)

いえいえ、ProductCollectionにはhas_many :products が設定してあるので、わざわざproduct_collection_items を joinすることを明記しなくてもよいです。

つまり下記のように書きます。

[90] pry(main)> ProductCollection.joins(:products)
  ProductCollection Load (0.9ms)  
  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`

=> [#<ProductCollection id: 1, title: "Ruby Collection", 
 created_at: "2013-10-21 15:55:51", updated_at: "2013-10-21 15:55:51">,
 #<ProductCollection id: 1, title: "Ruby Collection",
 created_at: "2013-10-21 15:55:51", updated_at: "2013-10-21 15:55:51">,
 #<ProductCollection id: 1, title: "Ruby Collection", 
  created_at: "2013-10-21 15:55:51", updated_at: "2013-10-21 15:55:51">,
 #<ProductCollection id: 2, title: "Rails Collection", 
 created_at: "2013-10-21 16:12:30", updated_at: "2013-10-21 16:12:30">,
 #<ProductCollection id: 3, title: "Unknown Collection", 
 created_at: "2013-10-21 16:14:51", updated_at: "2013-10-21 16:14:51">]

join はできました。次はwhere文書くところ。
まずは何も考えず書いてみます。

ProductCollection.joins(:products).where('products.open_status = 1')

うーん、実に微妙ですね。
何が微妙って、where区の中に文字列書いちゃってるところ。
今回は「有効な商品」の条件が簡単だから良いものの、業務アプリとかだと条件がとてつもなく複雑だったりしますよね。それを文字列で書いていたんじゃあ、なんだかSQL直接書いてるのと変わりないです。

しかも、「有効な商品」の条件を変えてくれ！なんていう仕様変更が来た場合、このwhere区の文字列をいちいち変えていかなければなりません。いろんな場所でこの条件を文字列べったりで埋め込んでいた場合、修正はとても面倒な作業です。
こういう仕様変更、実際にはよくある話です。

有効な商品を取得するために、最初は

Product.where(open_status: 1)

と書いていたとして、プロジェクトの途中で有効な商品の条件を

「Productのopen_statusが1の場合、かつ updated_atが10日以内のものに変えてください！」

と言われた場合、ちまちま全ての処理を

Product.where(open_status: 1).where(Product.arel_table[:updated_at].gt 10.days.ago)

に変えていく、なんていう作業、よくよく考えると普通やってないですよね？

そうです。こういう時にこそ、scopeの出番です。

Productクラスの中に、

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

というscopeを定義しておけば、有効な商品を取得するときは、

Product.available

で、すみますよね。

さて、このavailableというscope、「有効な商品を含んでいる商品コレクション(ProductCollection)のID一覧を取得したい！」という処理の中でも使いたいわけです。

こういう場合に利用するのがmergeです。
mergeを使うと、関連モデルのscopeが利用できるようになります。

ProductCollection.joins(:products).where('products.open_status = 1')

と書いていた処理が、mergeを使うと、

ProductCollection.joins(:products).merge(Product.available)

と書き直す事ができます。
これで「有効な商品」の条件が変わっても、Productのavailableスコープを書き直すだけで済みますね！

上記処理を実行すると、以下のようなSQLが発行されます。

[26] pry(main)> ProductCollection.joins(:products).merge(Product.available)
  ProductCollection Load (0.7ms) 
  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 `products`.`open_status` = 1
  AND `products`.`updated_at` > '2013-10-12 15:42:45'
=> [#<ProductCollection id: 1, title: "Ruby Collection",
  created_at: "2013-10-21 15:55:51", updated_at: "2013-10-21 15:55:51">,
 #<ProductCollection id: 1, title: "Ruby Collection", 
  created_at: "2013-10-21 15:55:51", updated_at: "2013-10-21 15:55:51">,
 #<ProductCollection id: 2, title: "Rails Collection", 
 created_at: "2013-10-21 16:12:30", updated_at: "2013-10-21 16:12:30">]

ほぼ完成ですね！あとは、select product_collections.* の部分を select product_collections.id にして、distinct指定すればいいだけです。

distinct指定は、distinctまたはuniqでできます。
idだけ取り出すには、pluckを利用します。

完成系は以下のような記述となります。

ProductCollection
  .joins(:products)
  .merge(Product.available)
  .uniq
  .pluck(:id)

結果は以下の通り。

[25] pry(main)> ProductCollection.joins(:products).merge(Product.available).uniq.pluck(:id)
   (0.5ms)  
  SELECT DISTINCT `product_collections`.`id` 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 `products`.`open_status` = 1 AND `products`.`updated_at` > '2013-10-12 15:41:04'
=> [1, 2]

期待した結果が得られましたね。やりました！

mergeを使えば、関連モデルのscopeが使い回せるようになります。

scopeを使うことで、どんな処理をしているのかがハッキリするし、変更に強いプログラムが書けるようになります。
このscopeを関連先でも使えるようになるmergeは、ActiveRecordの実装の中でも特にすばらしいものだと思っています。

皆さんもmergeを使いこなして、美しいプログラムを書きましょう！

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

2024年1月23日

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

#人事ブログ

ActiveRecord4でこんなSQLクエリどう書くの？ Merge編

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

＃人気の記事

＃タグ

＃アーカイブ