Multiple Text Search issue

Question

I created a search application using 2 columns (name and lastname) but is not working when a person has more than 2 names. How can i search a person with 3 names and lastname?

Controller:

class PersonController < ApplicationController

 def search

  @people = Person.find(:all,:conditions=>["(
                                                name LIKE ? OR  
                                            lastname LIKE ? OR
                      (concat(name, \" \", lastname) LIKE ? ) 
                                           )" ,
                                            "%#{params[:query]}%",
                                            "%#{params[:query]}%",
                                            "%#{params[:query]}%" ]
                                            )
  end

end

View

 <% form_tag :controller=>"person",:action=>"search" do %>
    Nombre o Apellido
    <%= text_field_tag "query",  params[:query]
 <% end %>

Example

 |name|                |Lastname|
 Marcos Ignacio Raul    Perez

I saw on my logs this: Works if i search Marcos Ignacio Raul Perez

 SELECT * FROM `people`
 WHERE ((
 name LIKE '%Marcos Ignacio Raul Perez%' OR 
 lastname LIKE '%Marcos Ignacio Raul Perez%'  
 OR (concat(name, " ", lastname) LIKE '%Marcos Ignacio Raul Perez%' )  
 ))

Works if i search Ignacio Raul Perez

 SELECT * FROM `people`
 WHERE ((
 name LIKE '%Ignacio Raul Perez%' OR 
 lastname LIKE '%Ignacio Raul Perez%'  
 OR (concat(name, " ", lastname) LIKE 'Ignacio Raul Perez%' )  
 ))

Works if i search Raul Perez

 SELECT * FROM `people`
 WHERE ((
 name LIKE '%Raul Perez%' OR 
 lastname LIKE '%Raul Perez%'  
 OR (concat(name, " ", lastname) LIKE 'Perez%' )  
 ))

Doesn't work if i search Marcos Perez

 SELECT * FROM `people`
 WHERE ((
 name LIKE '%Marcos Perez%' OR 
 lastname LIKE '%Marcos Perez%'  
 OR (concat(name, " ", lastname) LIKE '%Marcos Perez%' )  
 ))

Doesn't work if i search Ignacio Perez

 SELECT * FROM `people`
 WHERE ((
 name LIKE '%Ignacio Perez%' OR 
 lastname LIKE '%Marcos Perez%'  
 OR (concat(name, " ", lastname) LIKE '%Ignacio Perez%' )  
 ))

Doesn't work if i search Marcos Raul Perez

 SELECT * FROM `people`
 WHERE ((
 name LIKE '%Marcos Raul Perez%' OR 
 lastname LIKE '%Marcos Raul Perez%'  
 OR (concat(name, " ", lastname) LIKE '%Marcos Raul Perez%' )  
 ))

I will really appreciate help.

score 2 · Accepted Answer · answered Oct 04 '13 at 16:26

2

I highly recommend using elasticsearch with tire gem or something similar instead of SQL. It looks like your search requirements are complex. Problem that you are having with search in SQL is a sign of missing capabilities in underlying application architecture.

answered Oct 04 '13 at 16:26

Iuri G.

10,460
4
22
39

Luri i installed already this gem , but don't know how to integrate it on my project? can you help me? – Carlos Morales Oct 04 '13 at 16:28
http://railscasts.com/episodes/306-elasticsearch-part-1......... But is not complete and i'm using rails 2.3 – Carlos Morales Oct 04 '13 at 16:30
you do not need tire gem to interface with elastic search (since it uses JSON HTTP API), but it simplifies alot of your code since they abstract elastic search API very well. I recommend upgrading your project to rails 3 and using tire. If you dont want upgrade that you can look at http://www.elasticsearch.org/guide/en/elasticsearch/reference/current/docs-index_.html . that page gives you CURL examples for indexing and searching records. you can then implement your own library that will use Net::HTTP or RestClient to execute similar HTTP requests. – Iuri G. Oct 04 '13 at 18:23

score 0 · Answer 2 · answered Oct 22 '13 at 21:56

0

You can use Thinking sphinx is a good gem for rails search

answered Oct 22 '13 at 21:56

Charlie Brown

219
3
10

Multiple Text Search issue

2 Answers2