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?
I usually just define the method myself. def self.find_all_by_name(name) find(:all, :conditions => ["LOWER(name) = ?", name.downcase]) end On 4/20/06, Tim Uckun <timuckun@gmail.com> wrote:> > 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? > > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails > > >-------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails/attachments/20060420/3e9d0dbc/attachment-0001.html
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. On 4/20/06, Shane Sherman <wtf242@gmail.com> wrote:> I usually just define the method myself. > > def self.find_all_by_name(name) > find(:all, :conditions => ["LOWER(name) = ?", name.downcase]) > end > > > On 4/20/06, Tim Uckun <timuckun@gmail.com> wrote: > > > 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? > > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails > > > > > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails > > >
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/
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. On 4/22/06, Tom Copeland <tom@infoether.com> wrote:> 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/ > > > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
Tim Uckun wrote:> 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. > > On 4/22/06, Tom Copeland <tom@infoether.com> wrote: >> 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 >>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]
> -----Original Message----- > From: rails-bounces@lists.rubyonrails.org > [mailto:rails-bounces@lists.rubyonrails.org] On Behalf Of > Robby Russell > Sent: Monday, April 24, 2006 7:16 AM > To: rails@lists.rubyonrails.org > Subject: Re: [Rails] Rails + postgres case insensitive searches. > > > Tim Uckun wrote: > > 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. > > > > On 4/22/06, Tom Copeland <tom@infoether.com> wrote: > >> 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 > >> > > 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. > > RobbyAnd, if not, you can always setup an expression index for the lower() function on columns where you regularly do that. :) Regards, Dan This communication is the property of Qwest and may contain confidential or privileged information. Unauthorized use of this communication is strictly prohibited and may be unlawful. If you have received this communication in error, please immediately notify the sender by reply e-mail and destroy all copies of the communication and any attachments.
Philip Hallstrom
2006-Apr-26 02:16 UTC
[Rails] Rails + postgres case insensitive searches.
> I was really hoping to avoid such a thing if at all possible, not onlybecause 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... On 4/22/06, Tom Copeland <tom@infoether.com> wrote:> 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/ > > > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >