#343 Full-Text Search in PostgreSQL pro

rails db
rails g migration add_unaccent_extension
rake db:migrate
rails g migration add_search_index_to_articles
rm db/schema.rb
rake db:structure:dump
rake db:structure:load

select 'ninja turtles' @@ 'turtles';
select 'ninja turtles' @@ 'turtle';
select to_tsvector('ninja turtles') @@ plainto_tsquery('turtle');
select to_tsvector('english', 'ninja turtles') @@ plainto_tsquery('english', 'turtle');
select to_tsvector('simple', 'ninja turtles') @@ plainto_tsquery('simple', 'turtles');
select to_tsvector('simple', 'ninja turtles') @@ to_tsquery('simple', 'ninja & !turtles');
select ts_rank(to_tsvector('ninjaturtles'), to_tsquery('turtles'));

def self.text_search(query)
  if query.present?
    rank = <<-RANK
      ts_rank(to_tsvector(name), plainto_tsquery(#{sanitize(query)})) +
      ts_rank(to_tsvector(content), plainto_tsquery(#{sanitize(query)}))
    RANK
    where("to_tsvector('english', name) @@ :q or to_tsvector('english', content) @@ :q", q: query).order("#{rank} desc")
  else
    scoped
  end
end

class AddSearchIndexToArticles < ActiveRecord::Migration
  def up
    execute "create index articles_name on articles using gin(to_tsvector('english', name))"
    execute "create index articles_content on articles using gin(to_tsvector('english', content))"
  end

  def down
    execute "drop index articles_name"
    execute "drop index articles_content"
  end
end

gem 'texticle', require: 'texticle/rails'

search(query)

gem 'pg_search'

include PgSearch
pg_search_scope :search, against: [:name, :content],
  using: {tsearch: {dictionary: "english"}},
  associated_against: {author: :name, comments: [:name, :content]},
  ignoring: :accents

class AddUnaccentExtension < ActiveRecord::Migration
  def up
    execute "create extension unaccent"
  end

  def down
    execute "drop extension unaccent"
  end
end