PostgreSQL (Rails) - Reverse search (tagging, subscription)
Tags: postgres, rails, ruby
Published: 2020-11-12 09:17:01

---
title: PostgreSQL (Rails) - Reverse search (tagging, subscription)
published: true
date: 2020-11-03 09:11:03 UTC
tags: postgresql,rails,ruby
canonical_url: https://www.stefanwienert.de/blog/2020/11/03/postgresql-reverse-search/
cover_image: https://dev-to-uploads.s3.amazonaws.com/i/t0xnwhil2euhc70mzm0o.png
---

<toc></toc>

Using PostgresQL’s search capabilities (tsvector etc.) is a well-known low-key alternative to using a full blown search engine, like Elasticsearch/Solr/Algolia. But it turns out, it is also quite easily usable as a “reverse search engine”, what Elasticsearch calls “Percolate”. One use case could be user supplied “Subscriptions”, think of **“search subscriptions” of classified** that you want to trigger whenever a new item reaches the database.

After fiddling around one day with Elasticsearch percolators I was not so satisfied with the results, that I decided to trying PG’s tsvector/tsquery.

Recently, I’ve wanted to build a **keyword tagger** , that given a blob of text uses a predefined vocabulary database to give you the “most relevant” tags for that tags. The process should be absolutely the same as with search subscriptions by reversing Query (queries are stored in the DB) and Documents (only one document: the document to match with the queries). For this example I will use `keyword` matching as we implemented it in **Empfehlungsbund** in various places:

- Having a table `keywords` with a string column `keyword`, e.g. a managed table of most important keywords for your domain. In our cases that includes all the technolgy terms of developers/administrators (like “Javascript” or “Ruby on Rails”)
- there is also a little hierarchie of keywords in “competence groups” and we also add the “search relevance” which expresses how often this term is used by Job Seekers on our job platforms of Empfehlungsbund.

### 1. Adding TS-columns

1. We add a tsquery column `keyword_query` and a normalized token `keyword_search_token`

```sql
CREATE TABLE public.keywords (
    id bigint NOT NULL,
    keyword character varying,
    keyword_search_token character varying,
    keyword_query tsquery,
);

```

`keyword_query` if you follow several guides about PG’s fulltext search, I never came across one that proposed to just use a `tsquery` instead of a `tsvector`. But in our case (percalote) this makes total sense to add the string as `tsquery`.

`keyword_search_token` is a normalized version of our query, a processing pipeline that we ran before saving a record AND before tagging a document on that document later on. In our case the Pipeline consists of:

- Remove All HTML,
- normalize collocations (“java-developer” and “java developer”)
- Remove common (German) stop words,
- REPLACE important special chars that our search engine will need, like “.” (‘.NET’, “Vue.js”) or prefix/suffix ‘#’, ‘+’ (C++, C#). Many search engines does get this not right and strip out all special chars by default.

Here a Ruby snippet from our codebase:

```ruby
STOPWORDS = YAML.load_file('config/stopwords.yml')                                                                                                 
WORD_BOUNDARY = '(^|\s|[,\.\-\!\?])'
REPLACE_REGEX = /#{WORD_BOUNDARY}(#{STOPWORDS.join('|')})#{WORD_BOUNDARY}/i

REMOVE_STRING = [
  REPLACE_REGEX,
  %r{\([mwd/ ]+\)}
]

def preprocess_query(text)
   ActionController::Base.helpers.strip_tags(text).
      yield_self { |s| replace_special_chars(s) }.
      yield_self { |s| REMOVE_STRING.reduce(s) { |agg, elem| agg.gsub(elem, ' ') } }.
      # "Foo/Bar"
      gsub('/', ' / ').
      # EcmaScript5 -> Ecmascript 5
      gsub(/(\w+)(\d+)#{WORD_BOUNDARY}/, '\\1 \\2 \\3').
      tr('-', ' ').
      gsub(/\s+/, ' ').
      strip
end

def self.replace_special_chars(string)
  # C# C++, F#, ...
  string.
    gsub(/[#\+]/, '#' => "RAUTE", '+' => 'PLUS').
    # vue.js node.js, .net but not sentence dot
    gsub(/(\w+)?\.(\w+)/, '\\1DOT\\2')
  end

```

### 2. Adding a tsquery column and trigger

Now, we want to build a trigger that manages the keyword\_query conversion automatically. We can to this inline by splitting the words by whitespace and joining them back together with the infix **“<->”** operator, which means “the words on either side of the operator should be near together”. (Introduced in PG 9.6). This is extremely useful for searching for collocations like “Ruby on Rails”

```sql
CREATE FUNCTION public.keyword_results_before_insert_update_row_tr() RETURNS trigger
  LANGUAGE plpgsql
  AS $$
BEGIN
  new.keyword_query := to_tsquery(array_to_string(string_to_array(new.keyword_search_token, ' '), ' <-> '));
  RETURN NEW;
END;
$$;
CREATE TRIGGER keyword_results_before_insert_update_row_tr 
BEFORE INSERT OR UPDATE ON public.keyword_results 
FOR EACH ROW EXECUTE PROCEDURE  public.keyword_results_before_insert_update_row_tr();

```

### 2a. Rails tsquery migration

If you are using Rails you can use `hairtrigger` Gem to more easily create the trigger in a migration:

```ruby
add_column :keyword_results, :keyword_search_token, :string
add_column :keyword_results, :keyword_query, :tsquery
add_index :keyword_results, :keyword_query, using: 'gist'

create_trigger(compatibility: 1).on(:keyword_results).before(:insert, :update) do
  "new.keyword_query := to_tsquery(array_to_string(string_to_array(new.keyword_search_token, ' '), ' <-> '))"
end

```

BUT: ActiveRecord’s PG-Adapter at this moment does not know at the moment to handle `tsquery` columns and will fail with:

```
unknown OID 3615: failed to recognize type of 'keyword_query'. It will be treated as String.

```

And the table will be missing from `db/schema.rb`, and thus not loaded in test, with this comment:

```
# Could not dump table "keywords" because of following StandardError
# Unknown type 'tsquery' for column 'keyword_query'

```

The normal solution which you find for this class of problem is generally to switch to “SQL”-mode of Schema dumper. I don’t like that, because it makes the diffs gigantic and merges/rebases fail more often. I found, I could just tell Rails via patching how to handle that problem:

```ruby
# config/initializers/active_record_pg_types.rb
require "active_record/connection_adapters/postgresql_adapter"

module PGAddTypes
  # 1st place: how to handle this column = it's a string!
  def initialize_type_map(m = type_map)
    super
    m.register_type "tsquery", ActiveRecord::ConnectionAdapters::PostgreSQLAdapter::OID::SpecializedString.new(:tsquery)
    m.register_type(3615, ActiveRecord::ConnectionAdapters::PostgreSQLAdapter::OID::SpecializedString.new(:tsquery))
  end
end
# 2nd place:
ActiveRecord::ConnectionAdapters::PostgreSQLAdapter::NATIVE_DATABASE_TYPES[:tsquery] = { name: "tsquery" }

ActiveRecord::ConnectionAdapters::PostgreSQLAdapter.prepend PGAddTypes

# 3rd place: mapping a schema / migration line to a database mapping
module ActiveRecord::ConnectionAdapters::PostgreSQL::ColumnMethods
  def tsquery(*args, **options)
    args.each { |name| column(name, :tsquery, options) }
  end
end

```

With this code in place, schema-dumping and loading will work again!

### 3. Reverse searching against table

```ruby
preprocessed_query = preprocess_query(params[:query])
# create intermediate from table with our keywords and the preprocessed full_text:
from = KeywordResult.sanitize_sql_array ['keywords, to_tsvector(?) full_text', preprocessed_query]
KeywordResult.
  # where: having a match
  where(%{full_text @@ tsv_body}).
  from(from).
  order('score desc').
  # select: all normal columns + a "score"
  select("keywords.*, ts_rank(full_text, tsv_body) as score")

```

In SQL:

```sql
SELECT keywords.*, ts_rank(full_text, tsv_body) as score
FROM keyword_results, to_tsvector('Tag this string with keywords like Ruby on Rails') full_text
WHERE (full_text @@ tsv_body)
ORDER BY score desc

```

(Of course, you should NOT pass the string in raw like this but use prepared statements with a placeholder variable)

### Tips

#### Improving the accuracy by scoping

If you have a large spectrum of keywords there is the problem that some words can mean different things for different audiences. For example, when parsing a job ad or a applicant CV, imagine you find the word “Amazon” - For an IT person that probably is related to “AWS”, for a Retail staff maybe the Amazon Ad or Shops. Or think of generic greek/latin names for all kind of technologies (“Atlas”, “Prometheus” etc.) which could have all kind of meanings in different contexts, or roles like “Architect” or “Designer”. In our case we add the general sector to a keyword, like “IT” (programmer, architects, admins, tech designers), “Engineering”, “medicine”, “white-collar office” (Marketing, Sales, HR, Accounting) and similar. In a first step we first find, which of the Tags would have the most direct keyword matches and then only search for those keywords in this sector.

#### Suggest new keywords

As mentioned in the beginning, we have a 2 tier hierarchy of our keywords. Each keyword belongs to a “competence” (E.g. “Ruby on Rails”, “RSpec” belongs to “Ruby”). To suggest keywords we just take the keywords found, go up to the competence and then take the most relevant N keywords ordered by the “search popularity”.