Searching Over Multiple Columns
August 9, 2010 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.