How To … Potsgres fulltext search.

I came across in a play project to want to implement a search functionality on a postgres text column.

For Rails developers, there is a nice gem – pg_search – out there, it does all what is needed for it. But this is deeply bundled to Rails itself.

So I had to do it by hand, means:

1. Understanding what it is and how is done in PG.
2. How can this be integrated into my App?

For 1.

… this post full-text-search-ruby-rails-postgres is very informative for the beginning and understanding of the basics of the topic.

For 2.

For my app, it means a simple grape API, for the building of it grape-starter will be used. There I use ActiveRecord for accessing the database.

So that was the setup …

At the end of it, a model file exists, where the search will be implemented¹.

def self.search(query)
  find_by_sql([sql, query, query])
end

def self.sql
  <<-SQL.strip_heredoc
    SELECT
      id,
      data,
      created_at,
      updated_at,
      ts_rank(
        to_tsvector(',language', 'data'),
        to_tsquery(',language', '<query>')
      ) AS rank
    FROM zettel
    WHERE
      to_tsvector(',language', 'data') @@
      to_tsquery(',language', '<query>')
    ORDER BY rank DESC
    LIMIT 3
  SQL
end