Case insensitive with special special characters in Rails model-CodePudding

I have the cars_tables and in its name column I have names with special characters, for example:

car_names = Car.pluck :name
=> ["Cárrozería", "Óther Cars", "Forede Lúis", "Ságara Mbobe"]

The values are automatically parameterized making the special characters disappear

car_name_parameterize << ["Cárrozería", "Óther Cars", "Forede Lúis", "Ságara Mbobe"].map { |name| name.parameterize }.join(', ')
=> ["carrozeria", "other-cars", "forede-luis", "sagara-mbobe"]

and with the parameterized values I would like to do a query but I can't since the names have special characters that prevent me from doing so

first_car_name = car_name_parameterize.first
=> carrozeria
Car.find_by('name ILIKE ?', "%first_car_name%")
=> nil ;; nil because the word **carrozeria** doesn't have í special character, 
Car.find_by_name "carrozería"
=> #<Car:0x300312318 id: 1, name: "carrozería"...> ;; If it does the query without returning nil but it is because I consulted its name manually when placing "carrozería"

In short, I am looking to make the queries with the columns with the same name but with special characters (usually these characters usually have accents) recognized.

I am looking to make queries to the name of the cars table, canceling the special characters, such as the accent between the words for example

I have also tried the gsub method without success.

If you could help me I would be very happy and thank you for taking the time to read me.

CodePudding user response：

You will need to use the unaccent extension (docs: https://www.postgresql.org/docs/current/unaccent.html).

Basically, install the extension:

CREATE EXTENSION unaccent;

And then you will be able to use the unaccent() function:

where("unaccent(name) LIKE ?", "%#{your_value}%")

Read more details and different alternatives in the following entry: Postgres accent insensitive LIKE search in Rails 3.1 on Heroku

CodePudding user response：

Thanks to the link that the user @markets shared with me, I used the unaccent method to avoid the settlements and then the lower case and it worked for me.

cart_name = "carrozería"

Car.find_by("lower(unaccent(name)) LIKE ?", "%#{cart_name}%")