Ruby-on-rails Rails 模型中不区分大小写的搜索

Case-insensitive search in Rails model

提问by Jesper R?nn-Jensen

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 jeanscould be spelled Blue Jeans.

我现在从另一个数据集中导入一些产品参数，但名称的拼写不一致。例如，在另一个数据集中，Blue jeans可以拼写为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.

我想Product.find_or_create_by_name("Blue Jeans")，但这将创建一个新产品，几乎与第一个相同。如果我想查找和比较小写名称，我有哪些选择。

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.

性能问题在这里并不重要：只有 100-200 个产品，我想将其作为导入数据的迁移运行。

Any ideas?

有任何想法吗？

回答by alex.zherdev

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)

回答by oma

This is a complete setup in Rails, for my own reference. I'm happy if it helps you too.

这是 Rails 中的完整设置，供我自己参考。如果它对你也有帮助，我很高兴。

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?):

索引（来自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.

我希望有一种更漂亮的方式来做第一个和最后一个，但话说回来，Rails 和 ActiveRecord 是开源的，我们不应该抱怨 - 我们可以自己实现它并发送拉取请求。

回答by Viet

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.

如果您使用 Postegres 和 Rails 4+，那么您可以选择使用列类型 CITEXT，这将允许不区分大小写的查询，而无需写出查询逻辑。

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">

回答by Sohan

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.

请注意，默认设置是 :case_sensitive => false，因此如果您没有更改其他方式，您甚至不需要编写此选项。

Find more at: http://api.rubyonrails.org/classes/ActiveRecord/Validations/ClassMethods.html#method-i-validates_uniqueness_of

更多信息请访问：http: //api.rubyonrails.org/classes/ActiveRecord/Validations/ClassMethods.html#method-i-validates_uniqueness_of

回答by tomekfranek

In postgres:

在 postgres 中：

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

回答by Brad Werth

Several comments refer to Arel, without providing an example.

一些评论提到了 Arel，但没有提供示例。

Here is an Arel example of a case-insensitive search:

这是不区分大小写搜索的 Arel 示例：

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 (matcheswill use ILIKEfor Postgres, and LIKEfor everything else).

这种类型的解决方案的优点是它与数据库无关 - 它将为您当前的适配器使用正确的 SQL 命令（matches将ILIKE用于 Postgres 和LIKE其他所有内容）。

回答by Mike Woodhouse

Quoting from the SQLite documentation:

引用SQLite 文档：

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?

不是#find_or_create，我知道，它可能不是很跨数据库友好，但值得一看？

回答by Alex Korban

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.

另一种没有人提到的方法是将不区分大小写的查找器添加到 ActiveRecord::Base 中。可在此处找到详细信息。这种方法的优点是您不必修改每个模型，也不lower()必将子句添加到所有不区分大小写的查询中，您只需使用不同的 finder 方法即可。

回答by Dean Radcliffe

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 COLLATIONfor MSSQL, see NLS_SORT=BINARY_CIif using Oracle, etc.

大写字母和小写字母仅相差一位。搜索它们最有效的方法是忽略这一位，而不是转换lower或upper等。查看COLLATIONMSSQL的关键字，看看NLS_SORT=BINARY_CI是否使用Oracle等。

回答by superluminary

Find_or_create is now deprecated, you should use an AR Relation instead plus first_or_create, like so:

Find_or_create 现在已弃用，您应该使用 AR Relation 代替加上 first_or_create，如下所示：

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.

这将返回第一个匹配的对象，或者如果不存在则为您创建一个。