CODEWITHSUNDEEP
postgresqlsearchruby-on-rails-4full-text-searchrailscasts
DJF
DJF

Reputation: 47

Full text search not working completely on rails 4 PG 9.3

I have a problem with search on PG. I have a table with two columns first name and last name. I want someone to be able to search for one person using their first name, their last name, or the full name. I was following the Railscast #343 on full text search for PG and for what I understand, the search is supposed to work as I intend it to work but it doesn't. The code below will only return results when you put the exact first name or last name; it will not return anything is you put the first name and last name or if you only put a part of the first name or last name.

I would appreciate any input on how to make this work as I intend.

Model

def self.text_search(query)
    if query.present?
      where("first_name @@ :q or last_name @@ :q", q: query)
    else
      where(nil)
    end
end

Controller

def index
   @members = @organization.members.text_search(params[:query]).page(params[:page]).per(20)
end

Console Output

 SELECT COUNT(count_column) FROM (SELECT 1 AS count_column FROM "members" WHERE "members"."organization_id" = $1 AND (first_name @@ 'jon' or last_name @@ 'jon') AND (expiration_date <= '2014-11-30') AND (expiration_date > '2014-10-31') LIMIT 20 OFFSET 0)

Upvotes: 0

Views: 441

Answers (1)

jgraft
jgraft

Reputation: 938

Just a couple ideas on what might help get you what you are looking for.

First, on your query, I believe you should be using ILIKE to make the search case insensitive and also a %% wrap around your search term so that postgres knows to do a partial match. Here's an example from an autocomplete jquery text box I use. Link to the PG Documentation

def users
    search_term = params[:term]
    @users = current_account.users.where("name ILIKE ?", "%#{search_term}%").order(name: :asc)
    respond_to do |format|
        format.json { @users }
    end
end

The second suggestion I would make would be to use Ransack so that you can search multiple fields at once with the same search box. For example, if you were using Ransack you could do something like the following: Link to Ransack on Github

<%= search_form_for @q do |f| %>
 <%= f.label :first_name_or_last_name %>
  <%= f.search_field :first_name_or_last_name %>
  <%= f.submit %>
<% end %>

and then your controller would be as simple as:

def index
  @q = Member.search(params[:q])
  @members = @q.result(distinct: true)
end

To search for full name as well as first and last, I think you would have to create a field in your model called full_name and then you could easily add it to the search. I'm not sure how else you could do it without it actually being in the model.

Hope that helps!

Upvotes: 1

Related Questions