Search query for (name or forename) and (name forname) and (forname name)

Question

Im beginner in rails and have some problems to improve my search query:

In the controller i call:

def index
 if params[:search]
  @persons = Person.search(params[:search]).order("created_at DESC")
 else
  @persons = Person.order("created_at DESC")
 end
end

And in the model i have:

 def self.search(query)
  where("name like ?", "%#{query}%")
 end

So actually i only filter name! Now i tried to improve it but it didnt worked out how i liked it, my aim is that a user can type in for example:

 John
 Smith
 Smith John
 John Smith

and it always should return John Smith. So how do i write such a long sql query? Thanks in advance!

MrYoshiji · Accepted Answer · 2013-11-12T17:31:42.840

2

This search method should work for you:

def self.search(query)
  return where('FALSE') if query.blank?

  conditions = []
  search_columns = [ :vorname, :nachname ]

  query.split(' ').each do |word|
    search_columns.each do |column|
      conditions << " lower(#{column}) LIKE lower(#{sanitize("%#{word}%")}) "
    end
  end

  conditions = conditions.join('OR')    
  self.where(conditions)
end

This code is secure: sanitizes the strings before calling the SQL
This code is flexible: you can very easily add more columns to search on
This code is flexible: you can easily split on more than just space (- / | etc)
This code can be used for chain-scoping
This code is case-insensitive, will work with either John Smith or JohN SMITh

Don't hesitate to ask questions if needed!

edited Nov 12 '13 at 17:31

answered Nov 12 '13 at 16:38

MrYoshiji

54,334
13
124
117

I thinks thats the best code but somehow i get a error: `SQLite3::SQLException: near "ILIKE": syntax error: SELECT "patients".* FROM "patients" WHERE ( vorname ILIKE '%'emmanuel'%' OR nachname ILIKE '%'emmanuel'%' )` – John Smith Nov 12 '13 at 17:00
I updated my answer @JohnSmith -- Also I recommend you to use English in your applications, its easier to maintain for other people and it is kind of a part of the Rails Naming Convention ;) – MrYoshiji Nov 12 '13 at 17:03
Sorry but somehow i get the same error: `ActiveRecord::StatementInvalid in Patients#search ``SQLite3::SQLException: near "ILIKE": syntax error: SELECT "patients".* FROM "patients" WHERE ( vorname ILIKE '%Vera%' OR nachname ILIKE '%Vera%' )` – John Smith Nov 12 '13 at 17:10
The problem was the ILIKE function (does not exist in SQLite3), I updated my answer in consequence @JohnSmith – MrYoshiji Nov 12 '13 at 17:32
Yes now it works! Thank you very much! You helped me a lot! I only have one more little thing! The code you gave me makes many single queries i mean for example `name LIKE Search`. But maybe it woulb be better to split your code into two parts the first part only get triggerd when one search param is given for example `John` or `Smith` and the there could be another code that gets triggered when you submit two params like `John Smith` or `Smith John` and that looks if the two statements are like `name and forname`! I hope you understood me? – John Smith Nov 12 '13 at 17:42
My problem is that i generated a large database and noticed that when i pass for example `John Smith` i get so many entries like 40 simths and 100 other people with forname john! – John Smith Nov 12 '13 at 17:44
I started a new question: http://stackoverflow.com/questions/19937318/better-search-query-for-two-columns-forename-and-name Maybe you can help me with that? – John Smith Nov 12 '13 at 18:41

score 0 · Answer 2 · answered Nov 12 '13 at 16:41

0

What about doing something like this?

def self.search(query)
  where("name LIKE ? or forename LIKE ? or concat(name, ', ', forename) LIKE ?", "%#{query}%", "%#{query}%" , "%#{query}%")
end

If your search queries are getting really complex you might want to take a look at search gems, such as elasticsearch

answered Nov 12 '13 at 16:41

Nobita

23,519
11
58
87

I get a error: `SQLite3::SQLException: no such function: concat:` – John Smith Nov 12 '13 at 17:27
agh. I was assuming MySQL. Check this out: http://stackoverflow.com/questions/6134415/how-can-we-concatenate-string-in-sqlite in order to concatenate with Sqlite3. Having that said, I think @MrYoshiji answer should be the accepted one. Really nice solution – Nobita Nov 12 '13 at 17:30

usha · Answer 3 · 2013-11-12T17:20:43.550

0

What you have should work for the following search queries

John
Smith
John Smith

Do the following if you want it to work for reversed search queries also

def self.search(query)
  name_parts = query.split(' ')
  condition = name_parts.collect{|part| "name like #{sanitize("%#{part}%")}"}.join(' AND ')
  where(condition)
 end

edited Nov 12 '13 at 17:20

answered Nov 12 '13 at 16:41

usha

28,973
5
72
93

1

This code won't work for searches like `"pierre's car"` -> it will break the SQL query because of the quote ' ; Also the "AND" has no extra space and the SQL will look like this: `"name like '%whtvr%'ANDname like '%wtr%'ANDname ..."` -> won't work – MrYoshiji Nov 12 '13 at 16:46
Maybe you can help me with this one? Thanks http://stackoverflow.com/questions/19937318/better-search-query-for-two-columns-forename-and-name – John Smith Nov 12 '13 at 18:46

Search query for (name or forename) and (name forname) and (forname name)

3 Answers3