PostgreSQL Full-Text Search in Rails Applications

Full-text search is an essential feature in web applications where searching through large text content swiftly and accurately is a requirement. PostgreSQL offers robust full-text search functionality, which you can leverage in a Rails application to meet this need.

Setting Up Full-Text Search in PostgreSQL

First, ensure that your PostgreSQL database is set up correctly. Full-text search functionality in PostgreSQL depends on tsvector and tsquery, which are used to store and query data, respectively. Let’s walk through the key steps to integrate full-text search into a Rails application.

Step 1: Adding the Full-Text Search Column

Assume you have a table called articles with columns for title and body. First, we'll add a tsvector column to store the full-text search data:

ALTER TABLE articles ADD COLUMN tsv tsvector;

Now, populate this tsvector column with data from the title and body fields:

UPDATE articles SET tsv = to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''));

Step 2: Creating an Index

To allow efficient searches, you should index the tsvector column:

CREATE INDEX idx_fts ON articles USING gin(tsv);

Step 3: Trigger Update for Full-Text Search Column

To keep your full-text search data up-to-date, create a trigger that automatically updates the tsvector column whenever the title or body changes:

CREATE FUNCTION articles_tsv_update() RETURNS trigger AS $$
BEGIN
  NEW.tsv := to_tsvector('english', COALESCE(NEW.title, '') || ' ' || COALESCE(NEW.body, ''));
  RETURN NEW;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER tsvupdate BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW EXECUTE FUNCTION articles_tsv_update();

Integrating Full-Text Search in Rails

Adding Search to Your Rails Model

Once the database is set up, integrate full-text search in your Rails application within your model. Suppose you're in the Article model:

class Article < ApplicationRecord
  def self.search(query)
    where("tsv @@ plainto_tsquery(?)", query)
  end
end

This method allows you to search for terms across the title and body using the full-text search capabilities of PostgreSQL.

Implementing the Search in Your Controller

Incorporate a search form within your views and wire it up through the controller as follows:

class ArticlesController < ApplicationController
  def index
    if params[:query].present?
      @articles = Article.search(params[:query])
    else
      @articles = Article.all
    end
  end
end