Ransack search not working if there is 'space' in search term

Question

I am using ransack for search in my rails 3.2 application using postgres as database.

I have a Invoice model and every invoice belongs_to a buyer. Below is my search form in index page.

views/invoices/index.html.erb

<%= search_form_for @search do |f| %>
  <%= f.text_field :buyer_name_cont %>
  <%= f.submit "Search"%>
<% end %>

And here is my controller code.

controllers/invoices_controller.rb

  def index
    @search = Invoice.search(params[:q])
    @invoices=@search.result(:distinct => true).paginate(:page => params[:page], :per_page => GlobalConstants::PER_PAGE )

    respond_to do |format|
      format.html # index.html.erb
      format.json { render json: @invoices }
    end
  end

Let's say a invoice is there of a buyer having name "Bat Man".

If I search "Bat", I get the invoice in results. Again if I search "Man", I get the invoice in results. But if I search "Bat Man", I don't get the invoice in results.

I know it might be something trivial but I am not able to resolve.

Update

When I tried the sql query formed directly in database using pgAdmin, I realized that in database there were multiple spaces in the buyer name, something like "Bat.space.space.space.Man".

Can something be done so that "Bat.space.Man" search also finds "Bat.space.space.space.Man" in results?

Answer 1

You could sanitize your data. For instance with regexp_replace(). Run in the database once:

UPDATE invoice
SET    buyer = regexp_replace(buyer, '\s\s+', ' ', 'g')
WHERE  buyer <> regexp_replace(buyer, '\s\s+', ' ', 'g');

And sanitize new inserts & updates likewise.

\s .. class shorthand for "white space" (including tab or weird spaces).
The 4th parameter 'g' is for "globally", needed to replace all instances, not just the first.