Searching Over Multiple ColumnsAugust 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
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_onthat 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
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
Thanks to this answer on Stack Overflow for pointing me in the right(?) direction.