Rails: How can I take comma separated values and build an SQL query?

Question

Using a Rails 3 Active Record model (postgres db) I want to take a user search input of something like this:

"some string, some other string, final string"

From there I want to split strings on the commas and build an SQL query that looks something like this

SELECT * FROM items WHERE item_name SIMILAR TO '%(some string)%' OR item_name SIMILAR TO '%(some other string)%' OR item_name SIMILAR TO '%(final string)%'

I'm struggling to come up with a way to build this query as I am rather unfamiliar with the syntax of Ruby.

Answer 1

I would skip SIMILAR TO and go straight to POSIX regexes, I'm pretty sure SIMILAR TO will be translated to a regex internally so why bother? Also, ~ will let you use ANY to produce a nice readable expression. You could do something like this:

str   = 'some string, some other string, final string'
items = Item.where('item_name ~ any(array[?])', str.split(/\s*,\s*/))

That will end up running SQL like this:

select "items".* from "items" where item_name ~ any(array['some string', 'some other string', 'final string'])

and that will produce the same results as your SIMILAR TO version without a bunch of string wrangling.

If you're faced with a CSV string that can contain regex metacharacters then you probably want to throw some escaping in the mix. Backslashing anything that isn't alphanumeric should be safe enough:

str   = 'some string, some other string, final string'
pats  = str.split(/\s*,\s*/)
           .map { |s| s.gsub(/\p{^Alnum}/) { '\\' + $& } }
items = Item.where('item_name ~ any(array[?])', pats)

Switching to LIKE is also an option, then you'd only have to worry about _ and %:

str   = 'some string, some other string, final string'
pats  = str.split(/\s*,\s*/)
           .map { |s| s.gsub(/[_%]/, '%' => '\\%', '_' => '\\_') }
           .map { |s| '%' + s + '%' }
items = Item.where('item_name like any(array[?])', pats)

In real life you'd bust the escaping mess (and the "add LIKE percent signs" mess) out into utility methods to make your code cleaner.

If you don't care about case then you can use ~* or ilike for case insensitive pattern matching.

Answer 2

Try this way,

 string = 'some string, some other string, final string'
 patterns = search_string.split(", ").map{|str| "%#{str}%" }
 items = Item.where('item_name like any(array[?])', patterns)