0

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!

ndnenkov
  • 35,425
  • 9
  • 72
  • 104
Oles Malkov
  • 163
  • 8
  • Where is the "filter" hash coming from. Looks like SQLI prone to me. Can you store the values already downcased in the DB? Can you add additional indexes? – Pascal May 20 '16 at 14:31
  • @pascalbetz filter is a part of my custom structure that I send through REST from another app. Unfortunately I need to keep real case in database, but I almost fixed it with below suggestions. – Oles Malkov May 20 '16 at 15:22

3 Answers3

4
records.where("lower(#{filter['fieldName']}) in (?)", filter['value'].map(&:downcase))
ndnenkov
  • 35,425
  • 9
  • 72
  • 104
  • Looks like your answer is the closest :) `records = records.where('"' + filter['fieldName'] + '"' +" ILIKE ANY ( array[?] )", filter['value'].map {|value| "%#{value}%" })` here is what worked for me. Thanks! – Oles Malkov May 20 '16 at 15:53
  • 1
    @OlesMalkov you should post that as an answer and accept it. – ndnenkov May 20 '16 at 15:55
2

This is what worked for me:

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

Try this ......

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

Hope this will work for you.

Akshay Borade
  • 2,442
  • 12
  • 26
  • @pascalbetz, not sure how that should work as he is using `=` and not `in`. – ndnenkov May 20 '16 at 14:33
  • @OlesMalkov you are right. Should be "IN" instead of "=". Or he could use "similar to" as described here http://stackoverflow.com/questions/4928054/postgresql-wildcard-like-for-any-of-a-list-of-words – Pascal May 20 '16 at 15:56