CODEWITHSUNDEEP
ruby-on-railsrubypostgresqlrails-activerecord
Oles Malkov
Oles Malkov

Reputation: 163

Case insensitive search using IN operator in Ruby on Rails and PostgreSQL

I'm new to ROR and trying to implement search in PostgreSQL database using Active Record. I've found that to make search case insensitive I can use ILIKE operator instead of equals and LIKE but not sure what to do when I need to use IN operator.

I'm getting a field name and a collection of values which I need to check and case sensitive search works like that:

records = records.where(filter['fieldName'] => filter['value'])

where filter['value'] is an array.

Is there a way to update that line to make it case insensitive?

If no then I believe the only way is to loop through that array and split IN into many OR operations and use ILIKE for every single value in that array(however I'm not sure how to use OR with Active Record)?

Thanks!

Upvotes: 0

Views: 1822

Answers (3)

Akshay Borade
Akshay Borade

Reputation: 2472

Try this ......

records.where("lower(#{filter['fieldName']}) in ?", filter['value'].map(&:downcase))

Hope this will work for you.

Upvotes: 0

Oles Malkov
Oles Malkov

Reputation: 163

This is what worked for me:

records = records.where('"' + filter['fieldName'] + '"' +
                         " ILIKE ANY ( array[?] )", filter['value'].map {|value| "%#{value}%" })

Upvotes: 2

ndnenkov
ndnenkov

Reputation: 36101

records.where("lower(#{filter['fieldName']}) in (?)", filter['value'].map(&:downcase))

Upvotes: 4

Related Questions