How to do a case insensitive search with multiple elements?

Hi

I have a smallish problem

If a = [“Joe”,“John”]

then

Model.find_all_by_firstname(a)

works well.

Except when the case of the characters is in dispute - how can I make
the finder method case insensitive? All the discussions I find concern
themselves with the singular case, but not with the case of an array
being passed.

Thanks for reading this!

Pieter

Model.find_all_by_firstname(a.collect{|a| a.downcase})

On 15 July 2010 09:05, Rob N. [email protected] wrote:

Model.find_all_by_firstname(a.collect{|a| a.downcase})

Will that work if the names in the db include upper case chars? I
think the compare in the find needs to be case insensitive rather than
what is being compared against being forced to lower case.

Colin

Colin L. wrote:

On 15 July 2010 09:05, Rob N. [email protected] wrote:

Model.find_all_by_firstname(a.collect{|a| a.downcase})

Will that work if the names in the db include upper case chars? I
think the compare in the find needs to be case insensitive rather than
what is being compared against being forced to lower case.

Colin

If that’s the case, this will do it:

class Model

self.find_all_by_firstname(first_name)
  find(:all, :conditions => ["LOWER(firstname) = ?", 

first_name.downcase])
end

end

On Jul 15, 2010, at 7:18 AM, Rob N. wrote:

As long as you’re aware that using LOWER(first_name) will prevent any
use of an index on first_name.

You can also look at your particular database to see if it supports a
case-insensitive match. MySQL is case-insensitive by default (so I’m
guessing that’s not what you’re using :wink:

If you’re using PostgreSQL, you probably could find this via Google:

http://wiki.postgresql.org/wiki/FAQ#How_do_I_perform_regular_expression_searches_and_case-insensitive_regular_expression_searches.3F_How_do_I_use_an_index_for_case-insensitive_searches.3F

Note the part that discusses the CITEXT type and the use of an index
on the lower(column_name).

-Rob

Rob B.
[email protected] http://AgileConsultingLLC.com/
[email protected] http://GaslightSoftware.com/