Full Text Search in Postgresql

This article is the updated version of Railscast episode #341 to get the example working on Rails 5.1. This article uses PostgreSQL 9.6.2. You can check the version:

psql --version

Generate the article, author and comment models.

rails g model article name content:text published_at:datetime author_id:integer
rails g model author name
rails g model comment name content:text article_id:integer

The article.rb:

class Article < ApplicationRecord
  belongs_to :author
  has_many :comments

  def self.text_search(query)
    if query.present?
      where("name ilike :q or content ilike :q", q: "%#{query}%")
    else
      where(nil)
    end
  end
end

The scoped method is no longer available since Rails 4, so you can see the clunky where(nil) in the else clause. The author.rb:

class Author < ApplicationRecord
  has_many :authors
end

The comment.rb:

class Comment < ApplicationRecord
  belongs_to :article
end

The database.yml for postgres database:

development:
  adapter: postgresql
  encoding: unicode
  database: ftsp_dev
  pool: 5
  username: bparanj
  password:

test:
  adapter: postgresql
  encoding: unicode
  database: ftsp_test
  pool: 5
  username: bparanj
  password:

The seeds.rb:

bruce = Author.create! name: "Bruce Wayne"
clark = Author.create! name: "Clark Kent"

batman = Article.create! name: "Batman", author: bruce, published_at: 2.weeks.ago, content: <<-ARTICLE
Batman is a fictional character created by the artist Bob Kane and writer Bill Finger. A comic book superhero, Batman first appeared in Detective Comics #27 (May 1939), and since then has appeared primarily in publications by DC Comics. Originally referred to as "The Bat-Man" and still referred to at times as "The Batman", he is additionally known as "The Caped Crusader", "The Dark Knight", and the "World's Greatest Detective," among other titles. (from Wikipedia)
ARTICLE

superman = Article.create! name: "Superman", author: clark, published_at: 3.weeks.ago, content: <<-ARTICLE
Superman is a fictional comic book superhero appearing in publications by DC Comics, widely considered to be an American cultural icon. Created by American writer Jerry Siegel and Canadian-born American artist Joe Shuster in 1932 while both were living in Cleveland, Ohio, and sold to Detective Comics, Inc. (later DC Comics) in 1938, the character first appeared in Action Comics #1 (June 1938) and subsequently appeared in various radio serials, television programs, films, newspaper strips, and video games. (from Wikipedia)
ARTICLE

krypton = Article.create! name: "Krypton", author: clark, published_at: 5.weeks.ago, content: <<-ARTICLE
Krypton is a fictional planet in the DC Comics universe, and the native world of the super-heroes Superman and, in some tellings, Supergirl and Krypto the Superdog. Krypton has been portrayed consistently as having been destroyed just after Superman's flight from the planet, with exact details of its destruction varying by time period, writers and franchise. Kryptonians were the dominant people of Krypton. (from Wikipedia)
ARTICLE

lex_luthor = Article.create! name: "Lex Luthor", author: clark, published_at: 7.weeks.ago, content: <<-ARTICLE
Lex Luthor is a fictional character, a supervillain who appears in comic books published by DC Comics. He is the archenemy of Superman, and is also a major adversary of Batman and other superheroes in the DC Universe. Created by Jerry Siegel and Joe Shuster, he first appeared in Action Comics #23 (April 1940). Luthor is described as "a power-mad, evil scientist" of high intelligence and incredible technological prowess. (from Wikipedia)
ARTICLE

robin = Article.create! name: "Robin", author: bruce, published_at: 6.weeks.ago, content: <<-ARTICLE
Robin is the name of several fictional characters appearing in comic books published by DC Comics, originally created by Bob Kane, Bill Finger and Jerry Robinson, as a junior counterpart to DC Comics superhero Batman. The team of Batman and Robin is commonly referred to as the Dynamic Duo or the Caped Crusaders. (from Wikipedia)
ARTICLE

Comment.create! name: "Lois Lane", article: superman, content: <<-COMMENT
I want to meet Superman again, does anyone know where I can find him?
COMMENT

Comment.create! name: "The Joker", article: batman, content: <<-COMMENT
Haha, Batman, you will see your bat signal tonight!
COMMENT

Comment.create! name: "Robin", article: batman, content: <<-COMMENT
Enough with the games Joker.
COMMENT

Comment.create! name: "Riddler", article: batman, content: <<-CONTENT
Did someone say games?
CONTENT

The articles_controller.rb:

class ArticlesController < ApplicationController
  def index
    @articles = Article.text_search(params[:query])
  end

  def show
    @article = Article.find(params[:id])
    @comment = Comment.new(article_id: @article.id)
  end    
end

The comments_controller.rb:

class CommentsController < ApplicationController
  def new
    @comment = Comment.new
  end

  def create
    @comment = Comment.new(permitted_params)
    if @comment.save
      redirect_to @comment.article, notice: "Successfully created comment."
    else
      render :new
    end
  end

  private

  def permitted_params
    params.require(:comment).permit(:article_id, :article, :content, :name)
  end
end

The routes.rb:

Rails.application.routes.draw do
  resources :articles
  resources :comments

  root to: 'articles#index'  
end

The articles index page:

<h1>Articles</h1>

<%= form_tag articles_path, method: :get do %>
  <p>
    <%= text_field_tag :query, params[:query] %>
    <%= submit_tag "Search", name: nil %>
  </p>
<% end %>

<div id="articles">
<% @articles.each do |article| %>
  <h2>
    <%= link_to article.name, article %>
    <span class="comments">(<%= pluralize(article.comments.size, 'comment') %>)</span>
  </h2>
  <div class="info">
    by <%= article.author.name %>
    on <%= article.published_at.strftime('%b %d, %Y') %>
  </div>
  <div class="content"><%= article.content %></div>
<% end %>
</div>

The article show page:

<h1><%= @article.name %></h1>

<p class="author"><em>by <%= @article.author.name %> </em></p>

<%= simple_format @article.content %>

<p><%= link_to "Back to Articles", articles_path %></p>

<% unless @article.comments.empty? %>
  <h2><%= pluralize(@article.comments.size, 'comment') %></h2>

  <div id="comments">
  <% for comment in @article.comments %>
    <div class="comment">
      <strong><%= comment.name %></strong>
      <em>on <%= comment.created_at.strftime('%b %d, %Y') %></em>
      <%= simple_format comment.content %>
    </div>
  <% end %>
  </div>
<% end %>

<h3>Add your comment:</h3>
<%= render "comments/form" %>

The comments form:

<%= form_with(model: @comment) do |f| %>
  <% if @comment.errors.any? %>
    <div class="error_messages">
      <h2><%= pluralize(@comment.errors.count, "error") %> prohibited this comment from being saved:</h2>
      <ul>
      <% @comment.errors.full_messages.each do |msg| %>
        <li><%= msg %></li>
      <% end %>
      </ul>
    </div>
  <% end %>

  <%= f.hidden_field :article_id %>
  <div class="field">
    <%= f.label :name %><br />
    <%= f.text_field :name %>
  </div>
  <div class="field">
    <%= f.label :content, "Comment" %><br />
    <%= f.text_area :content, :rows => 12, :cols => 35 %>
  </div>
  <div class="actions"><%= f.submit %></div>
<% end %>

The comments new page:

<h1>New Comment</h1>
<%= render 'form' %>

You will not be able to see list of articles in the articles index page. You can also view each article and add comment to it. You can do search in the articles listing page and results will be displayed based on the simple ActiveRecord search query.

Search using pg_seach Gem

Add pg_search gem to the Gemfile and change the article model:

class Article < ApplicationRecord
  belongs_to :author
  has_many :comments

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

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

You can now search and it will work.

Using Textacular

The texticle gem is no longer maintained. The alternative is to use textacular gem. Add the gem to Gemfile and run bundle.

gem 'textacular', '~> 3.0'

Currently, textacular is not yet ready for Rails 5.1. You will get an error message from bundler:

Bundler could not find compatible versions for gem "activerecord":
  In snapshot (Gemfile.lock):
    activerecord (= 5.1.0)

  In Gemfile:
    rails (~> 5.1.0) was resolved to 5.1.0, which depends on
      activerecord (= 5.1.0)

    textacular (~> 3.0) was resolved to 3.0.0, which depends on
      activerecord (< 4.1, >= 3.0)

Running `bundle update` will rebuild your snapshot from scratch, using only
the gems in your Gemfile, which may resolve the conflict.

You can see that the highest version that is currently supported is ActiveRecord 41. You can download the source code for this article from: Full Text Search in Postgresql.

References


Related Articles


Create your own user feedback survey