課題メモより
has_manyやbelongs_toなどの関連を持っている他テーブルのデータを条件にして検索するには、joinsメソッドで他テーブルを結合して、whereで検索できるようです。
関連テーブルを結合するためのmergeメソッドというのもありますが使い方がよくわかりませんでした。色々な検索条件をどう実装するのか、使いながら覚えていこうと思います。
テーブル構造
練習のためにお互い関連をもつテーブルとモデルを作りました。
顧客、注文、商品の３つで、顧客は複数の注文を持ち、注文は商品をひとつだけ持ちます。
顧客マスタ(customer)
| カラム名 | 意味 |
|---|---|
| name | 氏名 |
商品マスタ(item)
| カラム名 | 意味 |
|---|---|
| name | 商品名 |
| price | 金額 |
注文テーブル(order)
| カラム名 | 意味 |
|---|---|
| costomer_id | 顧客ID |
| item_id | 商品ID |
| order_date | 注文日 |
| delivery_date | 納品日 |
モデルクラス
顧客モデル
class Customer < ActiveRecord::Base
has_many :orders
has_many :items, through: :orders
end
商品モデル
class Item < ActiveRecord::Base
has_many :orders
has_many :customers, through: :orders
end
注文モデル
class Order < ActiveRecord::Base
belongs_to :customer
belongs_to :item
end
特定の商品を注文した顧客を抽出
プルダウンで商品を選択して、その商品を注文したことのある顧客を一覧表示するような機能です。商品IDを渡して顧客のリストを返します。
# 引数の商品を注文したことがある
scope :has_item_id, -> item_id {
joins(:items).where('items.id = ?', item_id)
}
mergeメソッドを使うと、関連テーブルで定義されたスコープを使って検索条件を作ることができます。
商品モデルの方で、以下のようなスコープを定義しておいて、
# IDが一致
scope :id_is, -> id {
where(id: id)
}
商品モデルからマージすることができます。
# 引数の商品を注文したことがある
scope :has_item_id, -> item_id {
joins(:items).merge(Item.id_is item_id)
# # 直接whereを書いてマージすることもできる
# joins(:items).merge(Item.where(id: item_id))
}
商品モデルのid_isは、わざわざ定義する必要があるのかという感じですが、検索条件を、該当するモデルの中に閉じ込めておけるというのは素晴らしいです。
商品モデルに依存することを、商品モデルの外に出さずに実装できます。
複数の商品のうちどれかを注文した顧客を抽出
複数のIDを指定して、その中のどれかを注文したことのある顧客を抽出する場合も、同じように実装できました。whereメソッドにハッシュを渡すと、値が配列なら自動的に in条件で検索してくれるのでラクです。
# IDを含む
scope :id_in, -> ids {
where(id: ids) if ids.present?
}
# 引数の商品のうちどれかを注文したことがある
scope :has_item_id_in, -> item_ids {
joins(:items).merge(Item.id_in item_ids)
}
複数の商品全てを注文した顧客を抽出
一気に難しくなりました。
一つのクエリでできる気がしなかったので、商品Aを注文した顧客のリスト、商品Bを注文した顧客のリスト、商品Cの、、、を取得して、&で結合する。という方法で実現することはできましたが、これだと他の検索条件と組み合わせて再利用するということができません。
何かうまいやり方があれば教えてください。
解決しました。-> 指定した全ての要素をhas_many関連先に持つ条件の書き方
# 引数の商品のうち全てを注文したことがある
scope :has_item_id_all, -> item_ids {
item_ids.map { |item_id|
joins(:items).merge(Item.id_is item_id)
}.injdect(:&)
}
同じ商品を複数注文した顧客がいると結果がダブってしまう
例えば商品Aを注文したことがある顧客を抽出すると、以下のような結果になることがあります。
| 顧客 | 商品 |
|---|---|
| Aさん | カレーライス |
| Bさん | カレーライス |
| Bさん | カレーライス |
| Cさん | カレーライス |
顧客のリストは重複させたくないので、何も考えず.uniqをつけたところ
[54] pry(main)> Customer.has_item_id(5)
Customer Load (0.4ms) SELECT "customers".* ...
[55] pry(main)> Customer.has_item_id(5).uniq
Customer Load (0.4ms) SELECT DISTINCT "customers".* ...
^^^^^^^^
なんと！自動でDISTINCTをつけて実行されました。賢いですねえ。
商品名を部分一致で検索して購入したことのある顧客を抽出
IDで検索する場合と同じように実装できると思っていたのですが、、
# 名称が部分一致する
scope :name_like, -> name {
where('name like ?', "%#{name}%")
}
# 名称が部分一致する商品を注文した
scope :has_item_name_like, -> item_name {
joins(:items).merge(Item.name_like item_name)
}
以下のようなエラーが発生しました。
どうやらWHERE (name like 'マック')のnameが、商品名なのか顧客名なのか曖昧です。というエラーのようです。
[3] pry(main)> Customer.has_item_name_like 'マック'
Customer Load (0.6ms) SELECT "customers".* FROM "customers" INNER JOIN "orders" ON "orders"."customer_id" = "customers"."id" INNER JOIN "items" ON "items"."id" = "orders"."item_id" WHERE (name like '%マック%')
SQLite3::SQLException: ambiguous column name: name: SELECT "customers".* FROM "customers" INNER JOIN "orders" ON "orders"."customer_id" = "customers"."id" INNER JOIN "items" ON "items"."id" = "orders"."item_id" WHERE (name like '%マック%')
商品モデルのスコープで定義しているwhere('name like ?', "%#{name}%")の部分で、テーブル名を指定していないのが原因のようです。
whereにハッシュを渡した場合は、ActiveRecordがテーブル名を自動的につけてくれるので、エラーにはなりませんでした。しかしハッシュは=とinとbetweenにしか対応していません。
頭にテーブル名をつけてwhere('items.name like ?', "%#{name}%")とすれば、一応は動くのですが、他のモデルから使われることを想定して、whereに文字列を渡してその中でカラム名を使うときは全部頭にテーブル名をつけるようにする。というのも何だかイヤな感じです。
ActiveRecord4でこんなSQLクエリどう書くの? Arel編
上のリンクを参考にArelを使ったら全てが解決しました。
scope :name_like, -> name {
# where('name like ?', "%#{name}%")
where(arel_table[:name].matches "%#{name}%")
}
Arelを使うと外部結合やUNIONやEXISTSなんかもSQLを直書きせずに実装できるようです。
指定した金額以上の商品を購入したことのある顧客を抽出
これもArelを使って実装できました。
# 指定した金額以上
scope :price_gt, -> price {
where(arel_table[:price].gt price)
}
# 指定した金額以上の商品を注文した
scope :has_item_price_gt, -> price {
joins(:items).merge(Item.price_gt price)
}
まとめ
- 関連するテーブルの条件で検索するには
joinsでテーブルを結合する - 他のモデルに関する条件は他のモデルの中に記述して
mergeする。個々のモデルの役割をちゃんと分離する。 - そうすれば再利用性も高まるし複雑さも減る。
- 文字列でSQLを指定するとよくない。Arelがなんとかしてくれる。
- 複数の条件全てに該当するような検索はムズカシイ