Forum: Ruby on Rails ActiveRecord query for space-separated string

rails 3.2.11

My app needs a special query that searches an item from a Model.

Say, I have a model called Airport, having name:string field.
The app needs to find an airport by name.

One of the airport name is, for example,

"JFK New York USA"

Users may enter

"JFK"

well, this case it's easy.

But what if users enter

"jfk new" or "new york" or "JFK USA"  or "USA jfk"...

 so on?
Is it possible to develop a query to search corresponding
airport(s)?
Certainly some of them will end up with more than one result.

soichi

You should use something like a textual search. You can search for 
postgres
textual search, elasticsearch, solr. Or even if you want use a 
individual
query that hits the database you should try something like:

SELECT * FROM TABLE_NAME LIKE %"STRING_HERE"%

Sorry about the caps my intention was just get more highlight.

I hope it helps


2013/2/4 Soichi Ishida <lists@ruby-forum.com>

>
>  so on?  Is it possible to develop a query to search corresponding
> "Ruby on Rails: Talk" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to rubyonrails-talk+unsubscribe@googlegroups.com.
> To post to this group, send email to rubyonrails-talk@googlegroups.com.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
>


--
thiagocifani
http://about.me/thiagocifani <http://del.icio.us/thiagocifani>
<http://del.icio.us/thiagocifani>

I think you can frame your WHERE clause by splitting the search field
value on a particular database field. e.g.

Assume your query is passed in search parameter. So params[:search] will
access the string passed by the search form. Lets take the example of
Airport and name.


#get the parameter search and split it on blank spaces.
search_str = params[:search]
search_arr = search_str.split(' ')

#Loop through the search_arr and form a where_clause
where_clause = ''

search_arr.each_with_index do |s, i|
  where_clause += ' AND ' unless i == 0
  where_clause += "name LIKE '%" + s + "%'"
end

#Now use where method of ActiveRecord to fetch the records matching the
criteria.
airports = Airport.where(where_clause)


airport will have all the records matching thew criteria of name from
search form. Hope this will help. :)

Regards
Manoj Monga
manojm@mindfiresolutions.com
http://mindfiresolutions.com

Thanks everyone!

Manoj's way worked for me!

I appreciate it.

soichi