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