Case-insensitive search in Rails model

My product model contains some items

 Product.first
 => #<Product id: 10, name: "Blue jeans" >

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开发者_如何学Python.

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?

You'll probably have to be more verbose here

name = "Blue Jeans"
model = Product.where('lower(name) = ?', name.downcase).first 
model ||= Product.create(:name => name)

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.

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'
=> #<Product id: 1, name: "jOgGers">

Product.find_by(name: 'joggers')
=> #<Product id: 1, name: "jOgGers">

Product.find_by(name: 'JOGGERS')
=> #<Product id: 1, name: "jOgGers">

You might want to use the following:

validates_uniqueness_of :name, :case_sensitive => 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

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).

In postgres:

 user = User.find(:first, :conditions => ['username ~* ?', "regedarek"])

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> create table products (name string);
sqlite> insert into products values ("Blue jeans");
sqlite> select * from products where name = 'Blue Jeans';
sqlite> 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 => ['name LIKE ?', name])
    # update product or whatever
else
    prod = Product.create(:name => name)
end

Not #find_or_create, I know, and it may not be very cross-database friendly, but worth looking at?

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!

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.

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.

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.

An alternative can be

c = Product.find_by("LOWER(name)= ?", name.downcase)

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.

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 => 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.

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')

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 = Product.where(email: /^#{email}$/i).first

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 < 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
# => #<Product id: 1, name: "Blue jeans", created_at: "2016-03-27 01:41:45", updated_at: "2016-03-27 01:41:45"> 

You can use like this in model

scope :matching, lambda { |search, *cols|
    where cols.flatten.map{|col| User.arel_table[col].matches("%#{search}%") }.inject(:or)
}

and use wherever you like this

User.matching(params[:search], :mobile_number, :name, :email)

You can pass multiple column for search

for single column search you can use like this

User.where(User.arel_table[:column].matches("%#{search}%"))

If you're using postgres (probably others), I like this solution.

Product.find_by("name ilike 'bLue JEaNS'")

I like this better for a couple reasons.

1. Clearer connection to database action -> you can just copy paste that into where ...
2. If you choose to add a wildard %, it's straightforward.

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=>'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.

>> Product.create(:name => "Blue jeans")
=> #<Product id: 303, name: "Blue jeans">

>> Product.custom_find_by_name("Blue Jeans")
=> nil

>> Product.flush_custom_finder_cache!
=> nil

>> Product.custom_find_by_name("Blue Jeans")
=> #<Product id: 303, name: "Blue jeans">
>>
>> #SUCCESS! I found you :)