Ruby on Rails find_by case insensitive

Question

I need to find a record from 2 parameters but I need one of them to be case insensitive. The current case sensitive line is

  c = Course.find_by(subject_area: area, cat_number: cat)

But I need subject_area to be case insensitive. How would I achieve that?

Answer 1

It depends on the database, and you may need to pass in db-specific SQL for that (and not use find_by). Are you using postrges? if so, this would normally work:

Course.where("LOWER(subject_area) = ? AND cat_number = ?", area.downcase, cat)

alternatively you could convert your subject_area to downcase every time you save a new one... then just use:

Course.find_by(subject_area: area.downcase, cat_number: cat)

I could be wrong, but don't currently know of any rails-native way of doing a case insensitive rails find_by

Answer 2

An alternative can be

c = Course.find_by("LOWER(subject_area)= ? AND cat_number = ?", area.downcase, cat)

Answer 3

If you are using postgres, you could do something like:

> c = Course.find_by('subject_area ILIKE ? AND cat_number LIKE ?', area, cat)

(ILIKE is used to do insensitive search)

Answer 4

If you need to have this attribute be case-insensitive then the right way is to ensure it's always one particular in your model

before_save { self.subject_area = subject_area.downcase }

You can migrate your existing data to be downcased as well.

Having done that your find by operation still needs to ensure that the area is downcased in query as suggested. Course.find_by(subject_area: area.downcase, cat_number: cat)

Option 2: I knew I was forgetting this. Let's say you dont want to go migration path. Just define a scope. http://guides.rubyonrails.org/v4.1/active_record_querying.html#scopes

scope :area_nocase, (area) -> { where("LOWER(subject_area) = ?", area.downcase) }

then you can use it like

Course.area_nocase(area) or cat.course.area_nocase(area) or chain it however you need to.