Searching Over Multiple Columns

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



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



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.