适用于SQLite3 "LIKE"或PostgreSQL "ILIKE"的通用Ruby解决方案？

我正在使用SQLite3进行开发，使用PostgreSQL进行部署。但是，我面临以下问题：

我使用SQLite3进行简单搜索：

def self.search(search)
    if search
      find(:all, :conditions => ["style LIKE ? OR construction LIKE ?", "%#{search}%", "%#{search}%"])
    else
      find(:all)
    end
end

但是，它对PostgreSQL不起作用，我需要替换LIKE以便ILIKE解决问题：

def self.search(search)
    if search
      find(:all, :conditions => ["style ILIKE ? OR construction ILIKE ?", "%#{search}%", "%#{search}%"])
    else
      find(:all)
    end
end

是否有一种"Ruby方法"可以在任何数据库中进行这些搜索？

编辑 - 根据您的回答，我不相信我会为此找到通用的 Ruby 解决方案。

我遵循了 Ruby on Rails 教程：通过示例学习 Rails - 由 Michael Hartl 撰写，其中最终的 Gemfile 显示了两个数据库......好吧，令人失望...

问题的根源就在这里：

我正在使用SQLite3进行开发，使用PostgreSQL进行部署。

这是个坏主意™。你会不断遇到不兼容 - 或者更糟：在造成损害之前不要意识到一些.
使用相同的RDBMS（PostgreSQL）进行开发和生产，省去不必要的麻烦。

当您坚持不幸的设置时，有一个简单的解决方法：

lower(style) LIKE lower(?)

在两个平台上都同样有效。

如果提供小写搜索模式，请删除右侧lower()。

在标准SQLite中，lower(X)只折叠ASCII字母。引用SQLite手册中的核心功能一章：

lower（X）函数返回字符串 X 的副本，其中包含所有ASCII 字符转换为小写。默认内置 lower（）函数仅适用于 ASCII 字符。要对进行案例转换非 ASCII 字符，加载 ICU 扩展。

大胆强调我的。

PostgreSQL lower(X)开箱即用的 UTF-8 功能。

作为一个受欢迎的副作用，您可以在PostgreSQL中使用表达式lower(style)上的索引来加速该查询，这将比使用ILIKE和style上的基本索引更快。

此外，从PostgreSQL 9.1开始，您可以使用带有pg_trgm扩展名的GIN或GIST索引来加速任何LIKE和ILIKE查询 - 三元组索引不区分大小写。详：

自动完成字段的类似 UTF-8 字符串

我认为Arel是解决这个问题的最好方法。它被Rails用于活动记录，并且独立于数据库。您的代码在 sqlite3 或 postgres 中的工作方式相同，这似乎适合您的情况。使用 matches 方法将自动切换到 postgres 环境中的 ilike。例：

users=User.arel_table
User.where(users[:style].matches("%#{search}%").or(users[:construction].matches("%#{search}%")))

您可以从 github 获取更多信息：https://github.com/rails/arel/

不，没有"Ruby方法"来搜索数据库 - Ruby on Rails（特别是ActiveRecord）包含用于在ActiveRecord支持的RDB上执行CRUD操作的帮助程序方法，但是没有比您提供的示例更好的使用LIKE进行搜索的方法。

与此讨论相关的 Rails 文档部分是 ActiveRecord：：FinderMethods。

作为旁注，与其做find(:all)不如做所有事情。

Rails 文档使用的语法与执行 LIKE 语句的语法相同，即：

Person.exists?(['name LIKE ?', "%#{query}%"])

使用上述方法是完全安全的。

像下面这样的语句不安全的原因是，where字符串直接传递到数据库查询中，没有任何清理，这使您的数据库容易受到利用（即params[:first_name]中的简单撇号可能会弄乱整个查询并使数据库容易受到攻击 - 特别是SQL注入）。在上面的示例中，ActiveRecord 可以清理要传递到查询中的参数。

Client.where("first_name LIKE '%#{params[:first_name]}%'")

不幸的是，我认为你不会找到一个好的解决方案。除了：conditions 之外，唯一可用的其他语法是使用 .where 子句：

where(["style ILIKE ? OR construction ILIKE ?", "%#{search}%", "%#{search}%"])

不幸的是，您可能已经意识到，这种替代语法会遇到完全相同的问题。这只是开发环境与生产环境截然不同时会遇到的烦恼之一——SQLite和PostgresSQL之间还有其他相当实质性的差异。我建议只在您的开发机器上安装 Postgres 并使用它。这将使开发变得更加容易，并使你的代码更加干净。

虽然在生产和开发中使用不同的数据库不是一个好的做法，但使用 squeel gem 仍然是一个很好的案例：https://github.com/ernie/squeel/

有了它，

你可以用一个比原始SQL简单、可以说更干净易读的DSL来编写查询，并且gem将处理它到特定于所使用的RDBMS的SQL的转换。

瑞安·贝茨（Ryan Bates）有一个很好的视频：http://railscasts.com/episodes/354-squeel

我曾经遇到过同样的问题，这是我的解决方案：

我写了一个小库，可以为每个数据库使用该函数：

class AdapterSpecific
  class << self
    def like_case_insensitive
      case ActiveRecord::Base.connection.adapter_name
      when 'PostgreSQL'
        'ILIKE'
      else
        'LIKE'
      end
    end    
    def random
      #something
    end
  end

要在模型中使用它：

def self.search(search)
    if search
      find(:all, :conditions => ["style #{AdapterSpecific.like_case_insensitive} :query OR construction #{AdapterSpecific.like_case_insensitive} :query", {:query => "%#{search}%"}])
    else
      find(:all)
    end
end

自编写以来，我将部署数据库迁移到Postgres，因为出于多种原因，这是一个更好的设置（请参阅其他答案）。

您可能还考虑对 Postgres 使用全文搜索，这将使您的文本搜索更加高效，请参阅 texticle 了解基本实现或pg_search如果您需要更多自定义。

使用LIKE进行搜索可能会在数据库上很痛苦。当然，它不会使用成本过高的索引。

一个更长的答案：我建议在开发中使用Postgres（放弃sqlite3），然后通过Postgres的tsvector类型style, construction所有可搜索字段的全文索引。

使用索引时，Postgres 中的全文搜索非常快。

相关内容

最新更新

热门标签：