ActiveRecord#arel_tableでcondition作成

やりたいこと

• 複数の検索キーワードでor条件で検索したい
• クエリを一回で済ませたい

解決方法

• ActiveRecordのarel_talbe使ってやってみた
• 専用メソッドをmodelに作成

モデル

• Employee
  • id, kanji_name, kana_name, roman_alphabet_name, department_idカラムを持つ
• Departmentクラス
  • id, nameカラムを持つ

---

arel_tableでできること

• 検索条件の追加ができる
• 複雑な条件を付けるときに便利

使い方

• 基本

employee_arel_table = Employee.arel_table
conditions = employee_arel_table[:kanji_name].eq("田中")
Employee.where(conditions).first
#=> #<Employee id: 1, kanji_name: "田中" ... >

• 部署名にkeyが入って入れば、その部署のメンバー全員を取得する

key = "総務"
departments = Department.where("name like ?", key)
condition = employee_arel_table[:department_id].in(departments.ids)
Employee.where(condition)

• 複数の条件をorで繋ぐ

key = "田中"
conditions = []
[:kanji_name, :kana_name, :roman_alphabet_name].each do |name|
  conditions << employee_arel_table[name].matches(key)
end
condition = conditions.shift
conditions.each do |c|
  condition = condition.or c
end
Employee.where(condition)

---

作成したmodel

app/models/employee.rb

class Employee < ActiveRecord::Base
  belongs_to :department
  validates :kanji_name, :presence => true

  def self.where_all_type_name(keyword)
    raise ArgumentError if keyword.class != String
    raise ArgumentError if keyword.blank?

    employee_arel_table = self.arel_table
    key = "%#{keyword}%"
    conditions = []

    [:kanji_name, :kana_name, :roman_alphabet_name].each do |name|
      conditions << employee_arel_table[name].matches(key)
    end 
    departments = Department.where("name like ?", key)
    conditions << employee_arel_table[:department_id].in(departments.ids)

    condition = conditions.shift
    conditions.each do |c| 
      condition = condition.or c
    end 

    self.where(condition)
  end 
end

app/model/department.rb

class Department < ActiveRecord::Base
  has_many   :employees
  validates :name, :presence => true
end

• 使い方

@employees = Employee
keywords = ["hoge", "fuga", "piyo"]

keywords.each do |keyword|
  @employees = @employees.where_all_type_name(keyword)
end