Rails3ではSQLを扱うためにArelというヤツを使っています。
Rails2は使ったことがないですが、ActiveRecordのインターフェイスとして2と3では似ているようです。
そのためArelを使ってどのようなことができるのか、情報が少ないうえに新旧の情報が入り混じっているためになかなか調べるのが大変でした。
「ちょっと複雑なSQLを作ろうとすると直接SQLを書かなくてはいけなくなる」という内容の記事もよく見かけました。
デフォルトでは難しそうだと感じた私はプラグインを探してみて、 meta_search、その進化版のRansack、またSqueelも試してみました。
プラグインを使うと「簡単！」と思えるところと、逆に「難しい！」と感じるところとありました。
「SQLを直接かくか、、」と何度も思いましたが、ぐっとこらえてなんとかスマートに複雑な検索を実現できないかを試行錯誤しました。
■やりたいこと→人物の名前・メール・属性（性別など）から複数の条件で検索したい
・人物にはfirst_nameとlast_nameがあります。
Person（first_name, last_name)
・メールはusersテーブルに格納されていてPersonがuserをbelongs_toです。
belongs_to :user (email)
・属性はpattributesテーブルに格納されていてPersonとpattributesはhas_and_belongs_to_manyアソシエーションになっています。そしてアソシエーションテーブルとしてpattributes_peopleテーブルがあります。
has_and_belongs_to_many :pattributes
条件は
・keywordを入力しfirst_name, last_name, emailのいずれかにマッチ（LIKE）する
・pattributesを選択し、関連付けられているものにマッチする
・それらの条件の片方か、もしくは両方をAND検索する
ようするに
「名前かemailに「tom」とつく「男性」を検索する」
とかいうようにしたいわけです。
■問題点：条件ごとにコードを書くと、条件が増えるたびにコードが増える。
この場合は「キーワードのみ」「属性のみ」「キーワードと属性」の３通りに分けなければなりません。
■方針：条件を追加していって最後に検索をかけるようにする。
■試行錯誤１：
Person.whereの戻り値はActiveRecord::Relationなので下記のようにかけます。
@result = Person.where(条件１)
@result = @result.where(条件２)
こうすると条件を追加していくことができます。
しかしこの方法だと条件がANDになり今回の場合にそぐいません。
■試行錯誤２：
ORにするために次のように書きました。
@result = Person.where(条件１.or(条件2))
たしかにORになったのですが、これだと一行で書かなければならなくなるのでフリダシにもどります。
■試行錯誤３：ここがポイント！
この「条件１」とか「条件２」自体をオブジェクトとして変数に保存できないだろうかと考えました。
「条件」にあたる部分の書き方はいろいろあるのですが、下記のような書き方があります。
person_table = Person.arel_table
condition = person_table[:first_name].matches( “%#{keyword}%”)
このconditionはクラスを調べると[Arel::Nodes::Matches]であることがわかります。
ちなみにmatchesでなくてeqをつかうと[Arel::Nodes::Equality]クラスが返ります。
このconditionはオブジェクトなので変数として保存しやすいです。
そしてorやandでcondition同士を結合できます。
これはいけそうです。
■キーワードの条件を格納した配列を返すメソッド
[ror]
def ors_by_keyword
person_table = Person.arel_table
user_table = User.arel_table
#@result = @result.joins(:user)
@result = @result.joins(‘INNER JOIN "users" ON "users"."id" = "people"."user_id"’)
keywords = @keyword.strip.split(/[\s]+/)
ors = []
keywords.each do |keyword|
k = "%#{keyword}%"
ors << person_table[:first_name].matches(k)
ors << person_table[:last_name].matches(k)
ors << user_table[:email].matches(k)
end
ors
end
[/ror]
まず、Person.arel_tableでpeopleテーブルのArel::Tableオブジェクトを用意しています。
同様にUserに関しても用意します。
クエリでPersonに結び付けられたuserも参照することになるので、joinsを呼び出してjoin文を設定します。
ここで使われている@resultはあらかじめ用意しておいたActiveRecord::Relationオブジェクトです。
次にキーワードを空白で区切って複数のキーワードに分割します。
[“山田 タロウ”]だとしたら[“山田”, “タロウ”]という配列なるわけです。
そのキーワード分だけループします。
ループ内ではArel::Nodes::Matchesを必要な分だけ作成してorsという配列に格納します。
そして最終的にその配列を返します。
この配列にはArel::Nodes::Matchesが格納されているわけです。
■同様に属性の条件を格納した配列を返すメソッド（簡潔にしたコード）
[ror]
def ors_by_pattribute
pattribute_table = Pattribute.arel_table
@result = @result.joins(:pattributes).group(‘pattributes_people.person_id’)
pattribute_ids = @pattributes.collect {|x| x.to_s}
ors = []
pattribute_ids.each do | pattribute_id |
ors << pattribute_table[:id].eq(pattribute_id)
end
ors
end
[/ror]
キーワードのときとやっていることはほとんど同じです。
@pattributesの中はpattributesテーブルのidがFixnumで格納されています。
■検索のメソッド
[ror]
def search
ands = []
@result = Person.includes(:user)
ands << self.ors_by_keyword if @keyword.present?
ands << self.ors_by_pattribute if @pattributes.present?
conditions = generate_and_condition(ands)
@result = @result.where(conditions)
end
[/ror]
まず条件を格納するandsという配列を用意します。
@resultにはPerson.includes(:user)を実行してActiveRecord::Relationオブジェクトを格納しておきます。
次にキーワードがあればその条件の配列をandsに追加します。
同様に属性があればその条件の配列をandsに追加します。
conditionsには後述しますがwhereに渡すconditionsが格納されます。
■配列に格納した条件をconditionに展開します。
[ror]
def generate_and_condition(ands)
conditions = nil
ands.each do | ors |
condition = generate_or_condition(ors)
conditions = conditions ? conditions.and(condition) : condition
end
conditions
end
def generate_or_condition(ors)
condition = nil
ors.each do | c |
condition = condition ? condition.or(c) : c
end
condition
end
[/ror]
andsは[[条件,条件…],[条件,条件]]のように入れ子の配列になっているはずです。
この上位の階層がANDになり下位の階層がORの条件になります。
つまり[[条件 OR 条件 OR…] AND [条件 OR 条件 OR…]]という感じです。
今回の場合は [キーワードの条件] AND [属性の条件]となるわけです。
上記二つのメソッド generate_and_condition と generate_or_conditionはandsを走査させてandやorメソッドを使い、conditionsに条件を合体させているわけです。
このwhereメソッドの引数とするconditionsはArel::Nodesパッケージのさまざまなオブジェクトの塊になっています。
■最終的なSQLは下記のようになります。
SELECT “people”.* FROM “people” INNER JOIN “pattributes_people” ON “pattributes_people”.”person_id” = “people”.”id” INNER JOIN “pattributes” ON “pattributes”.”id” = “pattributes_people”.”pattribute_id” INNER JOIN “users” ON “users”.”id” = “people”.”user_id” WHERE ((((((“people”.”first_name” LIKE ‘%goro%’ OR “people”.”last_name” LIKE ‘%goro%’) OR “users”.”email” LIKE ‘%goro%’) OR “people”.”first_name” LIKE ‘%simane%’) OR “people”.”last_name” LIKE ‘%simane%’) OR “users”.”email” LIKE ‘%simane%’) AND (“pattributes”.”id” = 455 OR “pattributes”.”id” = 456)) GROUP BY pattributes_people.person_id ORDER BY updated_at LIMIT 3 OFFSET 0
■結論。
このような手順を踏むことで、条件の追加を容易にします。WHERE文の括弧の量がものすごく多いのは気になりますが、パフォーマンスの面では問題ないようです。