Forum: Ruby on Rails Rails + postgres case insensitive searches.

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Tim Uckun (Guest)
on 2006-04-20 07:16
(Received via mailing list)
Hello all

I am wondering how rails handles case sensitivity in databases. If I
do a Person.find_all_by_name("tim") in mysql I would expect to get
tim, TIm, and Tim.  Do I only get tim in postgres?

How do other people deal with this? Do you resort to find_by_sql for
all your postgres queries to get case insensitive results?
Shane Sherman (Guest)
on 2006-04-20 07:53
(Received via mailing list)
I usually just define the method myself.

def self.find_all_by_name(name)
  find(:all, :conditions => ["LOWER(name) = ?", name.downcase])
end
Tim Uckun (Guest)
on 2006-04-20 10:14
(Received via mailing list)
I guess that would work but it seems like a hassle to try and override
all the find_by methods. I wonder if it would be wise to override the
missing_method and intercept all find and find_by calls. I think I
remember the find_by methods are implemented via the missing method
intercept.

On the downside this means making a ton of function indexes in postgres.
Tom Copeland (Guest)
on 2006-04-21 21:20
(Received via mailing list)
On Thu, 2006-04-20 at 00:52 -0500, Shane Sherman wrote:
> I usually just define the method myself.
>
> def self.find_all_by_name(name)
>   find(:all, :conditions => ["LOWER(name) = ?", name.downcase])
> end
>

Or if you don't mind using PostgreSQL keywords:

def self.find_all_by_name(name)
  find(:all, :conditions => ["name ILIKE ?", name])
end

Yours,

Tom
http://tomcopeland.blogs.com/
Tim Uckun (Guest)
on 2006-04-24 12:45
(Received via mailing list)
I was really hoping to avoid such a thing if at all possible, not only
because it breaks the database abstraction but also because it
probably won't use an index.
Robby Russell (Guest)
on 2006-04-24 15:17
(Received via mailing list)
Tim Uckun wrote:
>>> end
>>>
>> Or if you don't mind using PostgreSQL keywords:
>>
>> def self.find_all_by_name(name)
>>   find(:all, :conditions => ["name ILIKE ?", name])
>> end
>>

You can also use a regular expression search.

find( :all, :conditions => [ 'name ~* ?', name ] )

I'm pretty sure that by using a regex... it'll use the index.

Robby

--
Robby Russell
Founder & Executive Director

PLANET ARGON, LLC
Ruby on Rails Development, Consulting & Hosting

www.planetargon.com
www.robbyonrails.com

+1 503 445 2457
+1 877 55 ARGON [toll free]
+1 815 642 4968 [fax]
Philip Hallstrom (Guest)
on 2006-04-26 04:17
(Received via mailing list)
> I was really hoping to avoid such a thing if at all possible, not only
because it breaks the database abstraction but also because it
probably won't use an index.

Check to see the actual query run from your development.log then pop
that
into psql with an "EXPLAIN" and see what postgres will do.

I'd be surprised if it didn't use the index, unless the stats indicate
it
shouldn't regardless...
This topic is locked and can not be replied to.