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

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

    t = OperatingSystem.arel_table

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

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

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.