Searching Over Multiple Columns

August 9, 2010

mysql arel rails3

I am developing an application where I collect system information from various hosts within our network and need to search those hosts. I would like to be able to search across multiple columns using 'or' but then return what matched.

The setup is pretty simple, I have a Host model that has_one OperatingSystem

class Host < ActiveRecord::Base
  has_one :operating_system
  attr_accessor :matched_on
end

For each model, I implement a 'search' class method, and for the OperatingSystem model, I implemented it as such:

class OperatingSystem < ActiveRecord::Base
  belongs_to :host

  def self.search(term)
    t = OperatingSystem.arel_table

    match_on = [:name,:version,:service_pack]

    matches = match_on.map {|m| "IF(#{t[m].matches("%#{term}%").to_sql},operating_systems.#{m.to_s},NULL)" }
    matching_string = "CONCAT_WS(' ', #{matches.join(",")}) as matched_on"
 
    results = OperatingSystem.select(
      "*",
      matching_string
    ).having("matched_on != ''").joins(:host)

    results.map do |os|
      host = os.host
      host.matched_on ||= []
      host.matched_on << os.matched_on
      host
    end
  end
end

What this does is construct a query using each value in match_on that says if that field matches the term show that otherwise show null. I then use MySQL's CONCAT_WS method to take each of the fields and combine them separated by a space and name it matched_on. Finally, I use a HAVING clause to filter out results that have a blank value for matched_on.

I then use a simple map to set the accessor on the host with what was matched and return the hosts that were matched.

I realize that this will only work with MySQL (probably), but since I am using it for development and production on this project that's not an issue, and it feels better than looping through the results again and setting matched_on

Thanks to this answer on Stack Overflow for pointing me in the right(?) direction.