使用Postgresql在Datamapper中不区分大小写，例如（ilike） [英] Case insensitive like (ilike) in Datamapper with Postgresql

问题描述

我们在Sinatra应用程序中使用Datamapper，并希望使用不区分大小写的代码，该代码对Sqlite（在开发中的本地环境）和Postgresql（在生产中的Heroku中）均适用。

We are using Datamapper in a Sinatra application and would like to use case insensitive like that works on both Sqlite (locally in development) and Postgresql (on Heroku in production).

我们有这样的语句

TreeItem.all(:name.like =>"%#{term}%",:unique => true,:limit => 20)

如果 term 是 BERL，我们从Sqlite和Postgresql后端得到建议 BERLIN。但是，如果 term 是 Berl，我们只能从Sqlite而不是Postgresql获得该结果。

If termis "BERL" we get the suggestion "BERLIN" from both the Sqlite and Postgresql backends. However if termis "Berl" we only get that result from Sqlite and not Postgresql.

我想这已经与dm-postgres-adapter和dm-sqlite-adapter都在结果SQL查询中输出 Like 的事实有关。由于Postgresql具有区分大小写的 Like ，因此我们得到了这种（对我们来说是不必要的）行为。

I guess this has to do with the fact that both dm-postgres-adapter and dm-sqlite-adapter outputting a LIKE in the resulting SQL query. Since Postgresql has a case sensitive LIKE we get this (for us unwanted) behavior.

有没有办法像Datamapper中那样使大小写不敏感，而不必诉诸于适配器使用原始SQL查询或修补适配器以使用 ILIKE 而不是 Like ？

Is there a way to get case insensitive like in Datamapper without resorting to use a raw SQL query to the adapter or patching the adapter to use ILIKEinstead of LIKE?

我当然可以在两者之间使用一些东西，例如：

I could of course use something in between, such as:

TreeItem.all(:conditions => ["name LIKE ?","%#{term}%"],:unique => true,:limit => 20)

推荐答案

通过编写自己的数据对象，在我们自己的代码中使用Postgresql，而不仅仅是作为适配器的配置。覆盖 like_operator 方法的适配器，我设法使Postgres的大小写不敏感 ILIKE 。

By writing my own data object adapter that overrides the like_operator method I managed to get Postgres' case insensitive ILIKE.

require 'do_postgres'
require 'dm-do-adapter'

module DataMapper
  module Adapters

    class PostgresAdapter < DataObjectsAdapter

      module SQL #:nodoc:
        private

        # @api private
        def supports_returning?
          true
        end

        def like_operator(operand)
          'ILIKE'
        end
      end

      include SQL

    end

    const_added(:PostgresAdapter)

  end
end

但是最终我还是决定移植有问题的应用程序以使用文档数据库。

Eventually I however decided to port the application in question to use a document database.

这篇关于使用Postgresql在Datamapper中不区分大小写，例如（ilike）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！