CODEWITHSUNDEEP
mysqlsqlruby-on-railsruby
Mark
Mark

Reputation: 6455

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

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?

Upvotes: 1

Views: 120

Answers (2)

engineersmnky
engineersmnky

Reputation: 29598

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 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

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%')

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%')

See Here for all the available Arel predications.

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

Upvotes: 3

Sebasti&#225;n Palma
Sebasti&#225;n Palma

Reputation: 33481

You can use the MySQL RLIKE operator, to match with an specific pattern you can create with your sentence.

sentence = 'White stoll bar'
@candidates = @candidates.where('title RLIKE ?', "(#{sentence.tr(' ', '|')})")

Upvotes: 3

Related Questions