Rails4におけるDB検索方法あれこれ - 小さな星がほらひとつ

Rails4で検索条件を付けてデータを取得しようとすると「ん？」となることが多いので、備忘録として改めてまとめておきたいと思います。

サンプルデータ

今回の記事では以下のフルーツテーブルを使います。

mysql> select * from fruits;
+----+-----------+-------+-------+---------------------+
| id | name      | price | stock | update_at           |
+----+-----------+-------+-------+---------------------+
|  1 | メロン    |  1000 |   300 | 2016-03-13 19:30:39 |
|  2 | ミカン    |   200 |   500 | 2016-03-13 19:30:55 |
|  3 | いちご    |   500 |    80 | 2016-03-13 19:31:06 |
|  4 | ぶどう    |   300 |    90 | 2016-03-13 19:31:29 |
+----+-----------+-------+-------+---------------------+

パターン1：ActiveRecord::Relationによるメソッドチェイン

Rails公式ガイドにも掲載されていて情報は豊富です。
Active Record クエリインターフェイス | Rails ガイド 

ActiveRecord::Baseのメソッドを呼び出すと、ActiveRecord::Relationオブジェクトが返されます。

Fruit.where("price >= 500")
  Fruit Load (0.8ms)  SELECT `fruits`.* FROM `fruits` WHERE (price >= 500)
=> #<ActiveRecord::Relation [#<Fruit id: 1, name: "メロン", price: 1000, stock: 300, update_at: "2016-03-13 19:30:39">, #<Fruit id: 3, name: "いちご", price: 500, stock: 80, update_at: "2016-03-13 19:31:06">]>

#ワイルドカードを用いて動的なパラメータの設定も可能
Fruit.where("price >= ? and stock >= ?",500,100)
  Fruit Load (2.9ms)  SELECT `fruits`.* FROM `fruits` WHERE (price >= 500 and stock >= 100)
=> #<ActiveRecord::Relation [#<Fruit id: 1, name: "メロン", price: 1000, stock: 300, update_at: "2016-03-13 19:30:39">]>

さらにActiveRecord::Relation同士でメソッドチェインが可能です。

Fruit.where("price >= ? and stock >= ?",500,100).order(:price).limit(5)
  Fruit Load (3.0ms)  SELECT  `fruits`.* FROM `fruits` WHERE (price >= 500 and stock >= 100)  ORDER BY `fruits`.`price` ASC LIMIT 5
=> #<ActiveRecord::Relation [#<Fruit id: 1, name: "メロン", price: 1000, stock: 300, update_at: "2016-03-13 19:30:39">]>

注意点として、ActiveRecord::Baseメソッドを実行するとSQLが即座に発行されます。

#この時点でSQLが発行され、resultにはActiveRecord::Relationインスタンスが格納される。
result = Fruit.where("price > 100")

#各レコードデータを取り出して利用することができる。
result[0].name
=> "メロン"

#返されたActiveRecord::Relationに対して繰り返しメソッドチェインが可能
result = result.where("stock >= 90").group("date(update_at)")
  Fruit Load (6.3ms)  SELECT `fruits`.* FROM `fruits` WHERE (price > 100) AND (stock >= 90) GROUP BY date(update_at)

この方法はシンプルで理解しやすいのですが、

or が利用できない
メソッドチェインを繰り返す度に裏ではSQLが発行されオーバーヘッドが発生する

といったデメリットがあります。

パターン2：Arel::TableからArel::Nodesの条件部のみを作成し、ActiveRecordへ渡す

前述のパターン1、内部的にはArelというライブラリを利用してSQLが組み立てらており、
それらは直接利用することができます。
GitHub - rails/arel: A Relational Algebra

#ActiveRecord::Baseのarel_tableメソッドによりArel::Tableインスタンスを取得
fruits = Fruit.arel_table
=> #<Arel::Table

#Arel::Tableのメソッドを利用して条件部だけを組み立てる。
fruits[:price].gteq(500)
=> #<Arel::Nodes::GreaterThanOrEqual

#得られる条件文
fruits[:price].gteq(500).to_sql
=> "`fruits`.`price` >= 500"

#Arel::NodesインスタンスをActiveRecord::Baseメソッドの引数に渡すことで結果が得られる
Fruit.where(fruits[:price].gteq(500))
  Fruit Load (1.0ms)  SELECT `fruits`.* FROM `fruits` WHERE (`fruits`.`price` >= 500)
=> #<ActiveRecord::Relation [#<Fruit id: 1, name: "メロン", price: 1000, stock: 300, update_at: "2016-03-13 19:30:39">, #<Fruit id: 3, name: "いちご", price: 500, stock: 80, update_at: "2016-03-13 19:31:06">]>

Arel::Nodes同士は「and」「or」を用いてメソッドチェインできます。

fruits = Fruit.arel_table

#条件1
cond1 = fruits[:price].gteq(300)
=> #<Arel::Nodes::GreaterThanOrEqual

#条件2
cond2 = fruits[:stock].gteq(90)
=> #<Arel::Nodes::GreaterThanOrEqual

#条件1 and 条件2
cond1.and(cond2).to_sql
=> "`fruits`.`price` >= 300 AND `fruits`.`stock` >= 90"

#条件1 or 条件2
cond1.or(cond2).to_sql
=> "(`fruits`.`price` >= 300 OR `fruits`.`stock` >= 90)"

ただしこの方法についても引数の渡し方が違うだけで、ActiveRecord::Base.whereを呼び出す度にSQLが発行され、
オーバーヘッドが発生します。

パターン3.SelectManagerを経由してActiveRecord::Base.find_by_sqlする

前述のパターン2で触れたArel::Tableですが、「project」「where」「group」「order」「take」といったメソッドを利用することで、
Arel::SelectManagerインスタンスを得ることができます。

fruits = Fruit.arel_table

#projectは選択列を指定するために利用する
fruits.project("*")
=> #<Arel::SelectManager

#得られるSQL
fruits.project("*").to_sql
=> "SELECT * FROM `fruits`"

Arel::SelectManager同士でメソッドチェインが可能です。
また、Arel::SelectManager自体は「生成されたSQL情報を持つだけ」であり、単体ではSQLの発行はされません。
そのため、パターン1、2のようなオーバヘッド無しでメソッドチェインが行えます。

fruits = Fruit.arel_table

fruits_chain = movies.project('*')

#以下、直前のSelectManager(fruits_chain)に対してメソッドチェインを繰り返す
fruits_chain = fruits_chain.where(fruits[:price].gteq(90))
fruits_chain = fruits_chain.where(fruits[:stock].gt(1))
fruits_chain = fruits_chain.group(fruits[:name])
fruits_chain = fruits_chain.order(fruits[:id])

#最終的なSQL
fruits_chain.to_sql
=> "SELECT * FROM `fruits` WHERE `fruits`.`price` AND `fruits`.`price` >= 90 AND `fruits`.`stock` > 1 GROUP BY `fruits`.`name`  ORDER BY `fruits`.`id`"

#ActiveRecord::Base.find_by_sqlにSQL文字列を渡して実際にSQLを発行する
result = Fruit.find_by_sql(fruits_chain.to_sql)
  Fruit Load (12.0ms)  SELECT * FROM `fruits` WHERE `fruits`.`price` AND `fruits`.`price` >= 90 AND `fruits`.`stock` > 1 GROUP BY `fruits`.`name`  ORDER BY `fruits`.`id`
=> [#<Fruit id: 1, name: "メロン", price: 1000, stock: 300, update_at: "2016-03-13 19:30:39">, #<Fruit id: 2, name: "ミカン", price: 200, stock: 500, update_at: "2016-03-13 19:30:55">, #<Fruit id: 3, name: "いちご", price: 500, stock: 80, update_at: "2016-03-13 19:31:06">, #<Fruit id: 4, name: "ぶどう", price: 300, stock: 90, update_at: "2016-03-13 19:31:29">]

#各レコードを参照できる
result[0].name
=> "メロン"

パターン4 find_by_sqlでゴリゴリにSQLを書く

流れはパターン3と一緒ですが、動的パラメータを設定する場合に少し注意が必要です。
find_by_sqlで動的パラメータを設定する際は、SQL文、パラメータをひとつの配列で渡す必要があります。

#SQL直書き + 動的パラメータ
Fruit.find_by_sql(["select fruits.name from fruits where fruits.price < ? and fruits.stock < ?", 1000, 100])
  Fruit Load (1.0ms)  select fruits.name from fruits where fruits.price < 1000 and fruits.stock < 100
=> [#<Fruit id: nil, name: "いちご">, #<Fruit id: nil, name: "ぶどう">]