filterrific : How to search query with multiple words?

Question

I'm using filterrific to make filter.

Let suppose I get an object with the locality set to "New York".

With the present code, it works if I search only with one word for example : "New". If I write "New York", nothing appears.

In the filterrific example the filter allow user to write several words in "query".

model

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

controller

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

view

  = 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"}

EDIT

logs

 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

The problem can be seen from your SQL log:

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

The desired SQL should be:

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

Because "new" is at the start of the locality, it matched; but because "york" is not at the start, it did not match.

To fix this, you could replace the line:

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

With:

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