文件:如何搜索多个单词的查询？

Question

我用filterrific做过滤器。

假设我得到一个locality设置为"New“的对象。

使用当前的代码，如果我只搜索一个单词(例如："New“)，它就能工作。如果我写“纽约”，什么都不会出现。

在美极了示例中，过滤器允许用户在“查询”中写几个单词。

模型

  scope :search_query, lambda { |query|
  return nil  if query.blank?
    terms = query.downcase.split(/\s+/)
    terms = terms.map { |e|
      (e.gsub('*', '%') + '%').gsub(/%+/, '%')
    }
    num_or_conds = 2
    where(
      terms.map { |term|
        "(LOWER(title) LIKE ? OR LOWER(locality) LIKE ?)"
      }.join(' AND '),
      *terms.map { |e| [e] * num_or_conds }.flatten
    )
  }



filterrific(
   default_filter_params: {},
   available_filters: [
     :search_query
   ]
 )

控制器

def index
   @filterrific = initialize_filterrific(
     @ads,
     params[:filterrific]
   ) or return
    @ads = @filterrific.find.page(params[:page])
end

视图

  = form_for_filterrific @filterrific do |f|
    div
      | Search
      = f.text_field :search_query, class: 'skills field w-input filterrific-periodically-observed', placeholder: "Job title, keywords, skills..."

      = f.submit 'GO !', {class: "submit-b w-button"}

编辑

日志

 Ad Load (1.2ms)  SELECT  "ads".* FROM "ads" WHERE (publishing_at <= '2018-05-24') AND (expiring_at >= '2018-05-24') AND ((LOWER(title) LIKE 'new%' OR LOWER(locality) LIKE 'new%') AND (LOWER(title) LIKE 'york%' OR LOWER(locality) LIKE 'york%')) LIMIT $1 OFFSET $2  [["LIMIT", 20], ["OFFSET", 0]]

Answer 1

从SQL日志可以看出这个问题：

...
AND ((LOWER(title) LIKE 'new%' OR LOWER(locality) LIKE 'new%')
AND (LOWER(title) LIKE 'york%' OR LOWER(locality) LIKE 'york%'))

所需的SQL 应该是：

...
AND ((LOWER(title) LIKE '%new%' OR LOWER(locality) LIKE '%new%')
AND (LOWER(title) LIKE '%york%' OR LOWER(locality) LIKE '%york%'))

因为"new“在locality的开头，所以它匹配；但是因为"york”不在开头，所以它不匹配。

要解决这个问题，您可以替换行：

(e.gsub('*', '%') + '%').gsub(/%+/, '%')

通过以下方式：

('%' + e.gsub('*', '%') + '%').gsub(/%+/, '%')