Question: Configure trigram and custom search with pg_search in rails

Question

Configure trigram and custom search with pg_search in rails

Answers 0
Added at 2016-12-02 09:12
Tags
Question

I implemented a search using pg_search gem on rails 4. Casecommons

When I, for example, searching word "programming" - it works fine, and when searching for "prog" it works because of 'prefix: true' in settings. But if I want to find "gram" or "gramming" or "rog" it shows nothing.

I need a configuration to search in the column with custom settings.

  1. I've got column 'code' and it should be found only by full_text
    without part_word. "12345" => "12345", "1234" => nil

  2. Column 'email' with part_word. For example "karamba@example.com" I want to find by "karamba", "ramba", "example", "example.com" etc.

  3. Other columns also by part word.

I tried to use dmetaphone, but it shows too many results, so I decide to stop only on trigram.

I'm not sure that I do the right way, maybe I miss some important part. So, I need help.

Here is my code:

gem 'pg_search'

Data base:

class InstallSomeContribPackages < ActiveRecord::Migration
  def change
    enable_extension "plpgsql"
    enable_extension "pg_trgm"
    enable_extension "fuzzystrmatch"
  end
end

Index:

CREATE INDEX trgm_idx ON users USING GIN (email gin_trgm_ops);

Model:

  class User < ActiveRecord::Base

  include PgSearch
  pg_search_scope :search, against: [:email, :code, :last_name, :first_name],
                                    :using => {
                                      tsearch: { prefix: true, any_word: true },
                                      :trigram => {}
                                    }

  def self.text_search(query)
    if query.present?
      search(query)
    else
      all
    end
  end  

Controller:

class Admin::UsersController < AdminController

  def index
    @users = User.text_search(params[:query]).page(params[:page])
  end
end

View:

<%= form_tag admin_list_users_path, method: :get do %>
<p>
  <%= text_field_tag :query, params[:query] %>
  <%= submit_tag "Пошук", name: nil %>
</p>
<% end %>

SQL:

SELECT  "users".* FROM "users" INNER JOIN (SELECT "users"."id" AS pg_search_id, (ts_rank((to_tsvector('simple', coalesce("users"."email"::text, '')$
 || to_tsvector('simple', coalesce("users"."second_name"::text, ''))), (to_tsquery('simple', ''' ' || 'tarzan' || ' ''' || ':*')), 0)) AS rank FROM "users" WHERE (((to_$
svector('simple', coalesce("users"."email"::text, '')) || to_tsvector('simple', coalesce("users"."second_name"::text, ''))) @@ (to_tsquery('simple', ''' ' || 'tarzan' |$
 ' ''' || ':*'))) OR ((coalesce("users"."email"::text, '') || ' ' || coalesce("users"."second_name"::text, '')) % 'tarzan'))) AS pg_search_7dfb4cf67742cb0660305e ON "us$
rs"."id" = pg_search_7dfb4cf67742cb0660305e.pg_search_id  ORDER BY pg_search_7dfb4cf67742cb0660305e.rank DESC, "users"."id" ASC LIMIT 25 OFFSET 0

Show me the trick please with the explanation.

Answers to

Configure trigram and custom search with pg_search in rails

Source Show
◀ Wstecz