Rails Query with ILIKE

Question

I have written a finder as follows:

@cars = @cars.joins(:manufacturers).where("manufacturers.name ILIKE ?", params[:manufacturer].gsub!(/-/, ' '))

params[:manufacturer] comes through in a form of a string that has been .parameterized by Rails.

The problem is that a string with an "'" or an "&" in it doesn't get matched by ILIKE correctly.

So as an example, some strings that are stored in my DB and their parameterized versions:

1. "This is a test" parameterized: "this-is-a-test" gsubbed: "this is a test"
2. "He didn't do it" parameterized: "he didn-t-do-it" gsubbed: "he didn t do it"
3. "This & That" parameterized: "this-that" gsubbed: "this that"

So when I do ILIKE between the first part of 2 and the third part of 2, it does not create a match. Same with 3. 1 obviously works fine though.

Any ideas how to get a correct match even with special characters in the strings?

Answer 1

This is the way to do it:

@cars = @cars.joins(:manufacturers).where("manufacturers.name LIKE ?", "%#{params[:manufacturer].parameterize}%")

By the way, you can do this, it looks cleaner:

search = params[:manufacturer].parameterize
@cars = @cars.joins(:manufacturers).where("manufacturers.name LIKE ?", "%#{search}%")

Answer 2

Since this is a lot similar to a slug system, you should just add a new field and call it whatever you find suitable, just don't forget to add an index so you don't waste time searching in strings.

Also you could add a before_create or before_save callback to auto create it when you save the object, in the format you are planning to search for.