我们在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)
发布评论