Ruby - Rails - SQL 查询 - 重新排序搜索词中的单词 [英] Ruby - Rails - SQL query - reordering words in a search term

问题描述

我正在处理一个带有搜索功能的项目，其设置如下:

I'm working on a project with a search function that is set up as follows:

if params[:search_term].present? && params[:search_term].length > 1
  @candidates = @candidates.where("title like ?","%#{params[:search_term]}%")
end

客户要求我放宽"搜索 - 特别是词序.目前如果有一个标题为White bar stool的候选人，搜索White stool bar，则不会返回任何结果.

The client has asked me to 'loosen up' the search - specifically the word order. At the moment if there is a candidate with a title of White bar stool and one searches for White stool bar, it returns no results.

有什么方法可以让我执行忽略词序的查询?或者我用不同的词序创建新的搜索词参数，进行多次搜索并合并结果会更好吗?

Is there any way for me to perform a query where word order is ignored? Or would it be better for me to make new search term params with a different word order, do multiple searches, and combine the results?

推荐答案

您可以考虑使用 Arel 为此.Arel 是 rails/activerecord 的底层查询汇编器(因此没有新的依赖项)，在构建复杂查询时非常有用，因为它提供的深度远比高级 ActiveRecord::QueryMethods.

You may consider using Arel for this. Arel is the underlying query assembler for rails/activerecord (so no new dependencies) and can be very useful when building complex queries because it offers far more depth than the high level ActiveRecord::QueryMethods.

Arel 提供了大量的预测匹配器选择，包括您的案例 matches_any 和 matches_all.这些方法采用 String 的 Array 并使用 Like 将它们拆分为单独的搜索条件.

Arel offers a large selection of predication matchers including in your case matches_any and matches_all. These methods take an Array of Strings and split them into individual search conditions using Like.

例如，搜索包含您可以使用的任何搜索词的所有候选词:

For Example to search for all candidates that contain any of the words searched for you can use:

class Candidate < ActiveRecord::Base
  def self.search_for(term)
    candidates = Candidate.arel_table
    where(
       candidates[:title].lower.matches_any(
          term.split.map { |t| "%#{t.downcase}%" }
       )
    )
  end
end

search_for 的最终结果(给定一个搜索词白色凳子")是:

The end result of search_for (given a search term of 'White stool bar') is:

SELECT [candidates].*
FROM [candidates]
WHERE (
  LOWER([candidates].[title]) LIKE '%white%' 
  OR LOWER([candidates].[title]) LIKE '%stool%' 
  OR LOWER([candidates].[title]) LIKE '%bar%')

这似乎是您要找的东西.如果它必须匹配所有术语，您可以改用 matches_all 这将导致:

Which appears to be what you are looking for. If it must match all the terms you can instead use matches_all which will result in:

SELECT [candidates].*
FROM [candidates]
WHERE (
  LOWER([candidates].[title]) LIKE '%white%' 
  AND LOWER([candidates].[title]) LIKE '%stool%' 
  AND LOWER([candidates].[title]) LIKE '%bar%')

请参阅此处了解所有可用的 Arel 预测.

See Here for all the available Arel predications.

这增加了基本转义的好处，以避免诸如 SQL 注入之类的事情.

This has added benefits of basic escaping to avoid things like SQL injection.

这篇关于Ruby - Rails - SQL 查询 - 重新排序搜索词中的单词的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！