multi term search in one field

[phron]

Not an issue I suppose but a lack of information/documentation on this...
I wiould like to do something like that :

 User.search({:profile_last_name_or_profile_first_name_cont_any=> 'jane doe'.split(' ')}).result

the SQL generated is the following

"SELECT "users".* FROM "users" LEFT OUTER JOIN "profiles" ON "profiles"."user_id" = "users"."id" WHERE ((("profiles"."last_name" ILIKE '%jane%' OR "profiles"."last_name" ILIKE '%doe%') OR ("profiles"."first_name" ILIKE '%jane%' OR "profiles"."first_name" ILIKE '%doe%')))"

which returns the expected results... But how to implement this request in my search form (in my view) and/or in my controller ?

My search form is :

<%= search_form_for @q, url: search_cockpit_users_path, method: 'post', id: 'users-search-form', class: 'span3 pull-right' do |su| %>
      <%= su.text_field :profile_last_name_or_profile_first_name_or_email_cont_any, :placeholder => 'full name or email contains' %>
      <%= su.submit 'Search', :name => nil, :class => 'btn ' %>
  <% end %>

As it is the SQL returned is

"SELECT "users".* FROM "users" LEFT OUTER JOIN "profiles" ON "profiles"."user_id" = "users"."id" WHERE ((("profiles"."last_name" ILIKE '%jane doe%') OR ("profiles"."first_name" ILIKE '%jane doe%')))"

but how to implement the split(' ') to have the expected SQL ?

Any help (and a bit of explanation) would be great, documentation is a bit light on the subject...

Cheers

[radar]

Why don't you take the parameter from the form and split it in the controller as you do in the first example?

[phron]

@radar
Of course it's the first thing I tried... But it does not work,
The search field string is converted as an SQL query between the submit action and before the search is effectively done and the result returned by the controller...

[radar]

@AnSo: Do you have an example app that I could try and reproduce this problem in? That would help me understand what's going wrong here and probably fix your problem.

[phron]

@radar : Ryan I have no example app. But the problem is easy to reproduce. (I paste the basic code here http://pastebin.com/JCruwhhp)

Two simplistic models User and Profile and a one-to-one relation between both. (User has_one Profile, Profile belongs_to User) and a users/index view, with search form in it and the users listing under the search form

Given you have 4 users (IDs 1, 2, 3, 4)

ID /profile.last-name / profile.first_name
1 DOE Jane
2 VEGA Vincent
3 DOE John
4 DUMMY Jane

If I type 'DOE' in the search field it returns users 1 & 3
If I type 'Jane" in the search field it returns users 1 & 4

If I type 'Jane DOE' it should returns

• user 1 (contains 'Jane' in profile.first_name & 'DOE' in profile.last_name),
• user 3 (contains 'DOE' in profile.last_name)
• user 4 (contains 'Jane' in profile.first_name)

but it returns nothing as the "where" query generated by ransack is

WHERE ((("profiles"."last_name" ILIKE '%jane doe%') OR ("profiles"."first_name" ILIKE '%jane doe%')))

Cheers

[rilian]

I am not sure if "search any" feature is supported in Ransack but in your case as a quick workaround you can split request with .split(' ') and pass all found words into grouping with or combinator , see example #169

[phron]

@rilian : I've seen this post but did not understand it. It is said : "I catch the params[:q] before passing it to the ransack engine and do some manipulations on it " ... How ? When ? Where ?

[rilian]

@AnSo, your controller fills in params variable (Hash)

your form use @q to store search params, which is fine

here is a workaround for any

# a bit dirty code
params[:q][:combinator] = 'or'
params[:q][:groupings] = []
custom_words = params.delete('profile_last_name_or_profile_first_name_or_email_cont_any')
custom_words.split(' ').each_with_index do |word, index|
  params[:q][:groupings][index] = {profile_last_name_or_profile_first_name_or_email_cont: word} 
end

@q = User.joins(:profile).search(params[:q])
@users = @q.result.paginate(:per_page =>10, :page => params[:page])
...

[phron]

@rilian : Aaaaaaah ok ! All is clear now ! Thanks a lot rilian you made my day better (and me a bit less idiot)!

[radar]

Thanks @rilian :)

[dimitrisdovinos]

very nice. thanks

[jonatack]

For query performance and simplicity, you can also store the various terms you want to search on in a combined search text column in your database (I use a citex field in postgres and remove accents and punctuation).

For example, first name + last name + email + phone stored as "JohnDoejohndoegmailcom1234567890"

Thus there is only one field to search on, and you can add a database index on the search query.

[dimitrisdovinos]

good idea. does this solution suffer from some 'false positives'? i.e. if you search with 'ilco' or 'hnDo' it would most likely come back with a result (which may or may not be ok)

[dimitrisdovinos]

I have posted a relevant question on stackoverflow http://stackoverflow.com/questions/19405454/mutliterm-search-in-ransack

[rilian]

i like @jonatack 's idea

@dimitrisdovinos you should decide yourself how to combine text data. it may be separated differentlyJohn Doe$$$johndoegmailcom$$$1234567890 to avoid false positives

[bverem]

Just curious if this was resolved. I'm also noticing a disparity between how the form method generates SQL for cont_any on multiple fields and how the ransack method generates SQL.

In my example, the expected behavior (console):

Model.ransack(submitter_name_or_tags_name_cont_any: %w(example tooth)).result.to_sql
=> "SELECT `cases`.* FROM `cases` LEFT OUTER JOIN ... WHERE ((`users`.`name` LIKE '%example%' OR `users`.`name` LIKE '%tooth%') OR (`tags`.`name` LIKE '%example%' OR `tags`.`name` LIKE '%tooth%'))"

The behavior when using :submitter_name_or_tags_name_cont_any within search_form_for with the setup used in the readme/wiki:

SELECT  `cases`.* FROM `cases` LEFT OUTER JOIN ... WHERE ((`users`.`name` LIKE '%example tooth%') OR (`tags`.`name` LIKE '%example tooth%'))

I followed something similar to the instructions above that alter the params, but I'm running into difficulties getting the altered params through will_paginate. I can get it to work by using params: params.permit! on my will_paginate helper, but that is certainly not my preference. I tried looking through the source code for this gem to see if there are ways to make the search form work like the console with no luck. Any thoughts? Is there any way I can make a search form use the same behavior as the console? Other than that, this gem is pretty fantastic. Thank you for all you do!

[sasharevzin]

@AnSo, your controller fills in params variable (Hash)

your form use @q to store search params, which is fine

here is a workaround for any

# a bit dirty code
params[:q][:combinator] = 'or'
params[:q][:groupings] = []
custom_words = params.delete('profile_last_name_or_profile_first_name_or_email_cont_any')
custom_words.split(' ').each_with_index do |word, index|
  params[:q][:groupings][index] = {profile_last_name_or_profile_first_name_or_email_cont: word} 
end

@q = User.joins(:profile).search(params[:q])
@users = @q.result.paginate(:per_page =>10, :page => params[:page])
...

In this case, the form with profile_last_name_or_profile_first_name_or_email_cont_any field will be empty. It won't show what was in the field before sending the form

[kaka-ruto]

Mine worked with
User.ransack(profile_last_name_or_profile_first_name_in: params[:keywords].split(', ')).result