Case-insensitive search in Rails model

Question

My product model contains some items   Product first    gt    lt Product id  10  name   Blue jeans   gt    I m now importing some product parameters from another dataset  but there are inconsistencies in the spelling of the names  For instance  in the other dataset  Blue jeans could be spelled Blue Jeans   I wanted to Product find or create by name  Blue Jeans    but this will create a new product  almost identical to the first  What are my options if I want to find and compare the lowercased name    Performance issues is not really important here  There are only 100-200 products  and I want to run this as a migration that imports the data   Any ideas

User · Accepted Answer

You ll probably have to be more verbose here  name    Blue Jeans  model   Product where  lower name        name downcase  first  model     Product create  name   gt  name

User · Answer

user   Product where email      email   i  first

User · Answer

Quoting from the SQLite documentation      Any other character matches itself or   its lower upper case equivalent  i e    case-insensitive matching       which I didn t know But it works   sqlite gt  create table products  name string   sqlite gt  insert into products values   Blue jeans    sqlite gt  select   from products where name    Blue Jeans   sqlite gt  select   from products where name like  Blue Jeans   Blue jeans   So you could do something like this   name    Blue jeans  if prod   Product find  conditions   gt    name LIKE     name         update product or whatever else     prod   Product create  name   gt  name  end   Not  find or create  I know  and it may not be very cross-database friendly  but worth looking at

User · Answer

You might want to use the following   validates uniqueness of  name   case sensitive   gt  false   Please note that by default the setting is  case sensitive    false  so you don t even need to write this option if you haven t changed other ways   Find more at  http   api rubyonrails org classes ActiveRecord Validations ClassMethods html method-i-validates uniqueness of

User · Answer

Find or create is now deprecated  you should use an AR Relation instead plus first or create  like so   TombolaEntry where  lower name        self name downcase  first or create name  self name    This will return the first matched object  or create one for you if none exists

User · Answer

Case-insensitive searching comes built-in with Rails  It accounts for differences in database implementations  Use either the built-in Arel library  or a gem like Squeel

User · Answer

Similar to Andrews which is  1   Something that worked for me is   name    Blue Jeans  Product find by  lower name        name downcase    This eliminates the need to do a  where and  first in the same query  Hope this helps

User · Answer

Upper and lower case letters differ only by a single bit  The most efficient way to search them is to ignore this bit  not to convert lower or upper  etc  See keywords COLLATION for MSSQL  see NLS SORT BINARY CI if using Oracle  etc

User · Answer

You can also use scopes like this below and put them in a concern and include in models you may need them   scope  ci find  lambda    column  value  where  lower   column         value downcase  first    Then use like this  Model ci find  column    value

User · Answer

There are lots of great answers here  particularly  oma s  But one other thing you could try is to use custom column serialization  If you don t mind everything being stored lowercase in your db then you could create     lib serializers downcasing string serializer rb module Serializers   class DowncasingStringSerializer     def self load value        value     end      def self dump value        value downcase     end   end end   Then in your model     app models my model rb serialize  name  Serializers  DowncasingStringSerializer validates uniqueness of  name   case sensitive   gt  false   The benefit of this approach is that you can still use all the regular finders  including find or create by  without using custom scopes  functions  or having lower name      in your queries   The downside is that you lose casing information in the database

User · Answer

So far  I made a solution using Ruby  Place this inside the Product model      return first of matching products  id only to minimize memory consumption    def self custom find by name product name        product names     Product all  select  gt  id  name         product names select  p  p name downcase    product name downcase  first   end     remember a way to flush finder cache in case you run this from console   def self flush custom finder cache        product names   nil   end   This will give me the first product where names match  Or nil    gt  gt  Product create  name   gt   Blue jeans     gt    lt Product id  303  name   Blue jeans  gt    gt  gt  Product custom find by name  Blue Jeans     gt  nil   gt  gt  Product flush custom finder cache    gt  nil   gt  gt  Product custom find by name  Blue Jeans     gt    lt Product id  303  name   Blue jeans  gt   gt  gt   gt  gt   SUCCESS  I found you

User · Answer

Some people show using LIKE or ILIKE  but those allow regex searches   Also you don t need to downcase in Ruby   You can let the database do it for you   I think it may be faster   Also first or create can be used after where     app models product rb class Product  lt  ActiveRecord  Base      case insensitive name   def self ci name text      where  lower name    lower      text    end end    first or create can be used after a where clause Product ci name  Blue Jeans   first or create   Product Load  1 2ms   SELECT   products    FROM  products   WHERE  lower name    lower  Blue Jeans     ORDER BY  products   id  ASC LIMIT 1     gt    lt Product id  1  name   Blue jeans   created at   2016-03-27 01 41 45   updated at   2016-03-27 01 41 45  gt

User · Answer

If you are using Postegres and Rails 4   then you have the option of using column type CITEXT  which will allow case insensitive queries without having to write out the query logic   The migration   def change   enable extension  citext   change column  products   name   citext   add index  products   name  unique  true   If you want to index the product names end   And to test it out you should expect the following   Product create  name   jOgGers    gt    lt Product id  1  name   jOgGers  gt   Product find by name   joggers     gt    lt Product id  1  name   jOgGers  gt   Product find by name   JOGGERS     gt    lt Product id  1  name   jOgGers  gt

User · Answer

This is a complete setup in Rails  for my own reference  I m happy if it helps you too   the query   Product where  lower name        name downcase  first   the validator   validates  name  presence  true  uniqueness   case sensitive  false    the index  answer from Case-insensitive unique index in Rails ActiveRecord     execute  CREATE UNIQUE INDEX index products on lower name ON products USING btree  lower name       I wish there was a more beautiful way to do the first and the last  but then again  Rails and ActiveRecord is open source  we shouldn t complain - we can implement it ourselves and send pull request

User · Answer

An alternative can be  c   Product find by  LOWER name       name downcase

User · Answer

Another approach that no one has mentioned is to add case insensitive finders into ActiveRecord  Base  Details can be found here  The advantage of this approach is that you don t have to modify every model  and you don t have to add the lower   clause to all your case insensitive queries  you just use a different finder method instead

User · Answer

In postgres    user   User find  first   conditions   gt    username         regedarek

User · Answer

Assuming that you use mysql  you could use fields that are not case sensitive  http   dev mysql com doc refman 5 0 en case-sensitivity html

User · Answer

Several comments refer to Arel  without providing an example   Here is an Arel example of a case-insensitive search   Product where Product arel table  name  matches  Blue Jeans      The advantage of this type of solution is that it is database-agnostic - it will use the correct SQL commands for your current adapter  matches will use ILIKE for Postgres  and LIKE for everything else

[ruby-on-rails] Case-insensitive search in Rails model

Examples related to ruby-on-rails

Examples related to activerecord

Examples related to case-insensitive