Ruby on Rails - Search Query for words within the title

Question

In my application I am doing a scope/search on :title for a search/filter of my records. The search itself works fine, only thing is that user need to write exactly the title & they can't search word within the :title.

For instance If the title is: This search is cool, user need to start the search and have the complete sentence: This search to search and they can't write is cool and get records that have is cool in the title.

My scope looks like:

class Post < ActiveRecord::Base

  scope :search_query, lambda { |query|
    return nil  if query.blank?
    # condition query, parse into individual keywords
    terms = query.downcase.split(/\s+/)
    # replace "*" with "%" for wildcard searches,
    # append '%', remove duplicate '%'s
    terms = terms.map { |e|
      (e.gsub('*', '%') + '%').gsub(/%+/, '%')
    }
    # configure number of OR conditions for provision
    # of interpolation arguments. Adjust this if you
    # change the number of OR conditions.
    num_or_conditions = 1
    where(
        terms.map {
          or_clauses = [
              "LOWER(posts.title) LIKE ?"
          ].join(' OR ')
          "(#{ or_clauses })"
        }.join(' AND '),
        *terms.map { |e| [e] * num_or_conditions }.flatten
    )
  }

How can I make my scope/query so user can search words within the title and get records that has words they have searched for?

I tried with ILIKE, but then the search stop working in development, I think its because of sqlite can't have ILIKE, but in productionthe search worked but still can't search for words within titles.

When I use LIKE, the sql query was:

SELECT COUNT(count_column) FROM (SELECT  1 AS count_column FROM "posts" WHERE ((LOWER(posts.title) LIKE 'rails%')) LIMIT 50 OFFSET 0) subquery_for_count

While when I used ILIKE, the query was:

SELECT COUNT(count_column) FROM (SELECT  1 AS count_column FROM "posts" WHERE ((LOWER(posts.title) ILIKE 'rails%')) LIMIT 50 OFFSET 0) subquery_for_count

SQLite3::SQLException: near "ILIKE": syntax error: SELECT COUNT(count_column) FROM (SELECT  1 AS count_column FROM "posts" WHERE ((LOWER(posts.title) ILIKE 'rails%')) LIMIT 50 OFFSET 0) subquery_for_count

ps: Im using Filterrific gem

I use pg gem for Production ENV & sqlite3 for Development ENV

Answer 1

As its described in this w3schools article, LIKE works as:

WHERE CustomerName LIKE 'a%' => Finds any values that starts with "a"
WHERE CustomerName LIKE '%a' => Finds any values that ends with "a"
WHERE CustomerName LIKE '%or%' => Finds any values that have "or" in any position
WHERE CustomerName LIKE '_r%' => Finds any values that have "r" in the second position
WHERE CustomerName LIKE 'a_%_%' =>  Finds any values that starts with "a" and are at least 3 characters in length
WHERE ContactName LIKE 'a%o' => Finds any values that starts with "a" and ends with "o"

I needed to change (e.gsub('*', '%') + '%').gsub(/%+/, '%'), to: ('%' + e.gsub('*', '%') + '%').gsub(/%+/, '%') .

When searching with (e.gsub('*', '%') + '%').gsub(/%+/, '%'), result would be (LOWER(posts.title) ILIKE 'keyword%'), where as ('%' + e.gsub('*', '%') + '%').gsub(/%+/, '%'), would give (LOWER(posts.title) ILIKE '%keyword%')