Maybe add postgres full text search helpers

[paulcsmith]

https://pganalyze.com/blog/full-text-search-ruby-rails-postgres

See an example implementation here: https://github.com/wezm/read-rust/blob/f8b50f2c59d9e0454362be43bb0f4d3f66a8860b/crystal/src/queries/post_query.cr

https://www.postgresql.org/docs/11/textsearch-controls.html see "websearch_to_query"

[srcrip]

I would be very interested in contributing/working together with someone on this if there's anyone else really into this.

[jwoertink]

@Sevensidedmarble I'd love to see this too. The main things when contributing are that it provides a solution in a type safe way, and also allows for an escape hatch for people to get a little crazy. (e.g. ThingQuery.new.name("") VS ThingQuery.new.where("name = ?")).

I haven't put any thoughts in to this feature yet, but if you'd like to propose an API in this thread, we can sort of work out what may or may not work..

[srcrip]

Is there anything anyone absolutely doesn't like about the implementation in the pg_search gem? That would be a good starting point for API ideas.

Personally I like their API ideas but I don't like how they've split up 'search scopes' vs 'multi search'. It'd be nice to just have one unified way of defining a document. But maybe I'm biased because my use case is multi-table.

Something like:

class Question < BaseModel
  search document: :question_and_answers,
          against: [:content, :title]
               if: :should_be_searched?
end

class Answer < BaseModel
  search document: :question_and_answers,
          against: [:content]
end

class Reply < BaseModel
  # If :document is unset it will be a single table document
  search against: [:body]
end

Different search types

The pg search gem also supports trigrams and dmetaphone in addition to tsvectors. I personally think supporting only tsvector is fine but whats other peoples opinion on that?

[srcrip]

I also love the approach https://pganalyze.com/blog/full-text-search-ruby-rails-postgres takes by using generated columns in postgres 12 for single table search. I think that would be a great way to approach searching on a single table here.

[BrucePerens]

So, I am coding these with where() today. I suggest implementing MyClassQuery.new.field.trigram(String) and MyClassQuery.new.field.regex(String), as there are many ways of searching but those two cover the bases of efficiency and generality.

Here is how I am set up for trigrams in my migration:

def migrate
   enable_extension "pg_trgm"
   ...
   create index company_name_trigrams on companies using gin (name gin_trgm_ops);
end

This is my operation for general searches using trigrams:

class GeneralSearch < Avram::Operation
  attribute text : String
  attribute search_companies : Bool
  attribute search_products : Bool
  attribute search_brands : Bool
  before_run :do_validation

  def do_validation
    validate_required text
  end

  def run : SearchResult
    s = SearchResult.new
    
    if search_companies.value
      s.companies = CompanyQuery.new.where("name % ?", text.value).preload_url_path.to_a
    end
    if search_brands.value
      s.brands = BrandQuery.new.where("name % ?", text.value).preload_url_path.to_a
    end
    if search_products.value
      s.products = ProductQuery.new.where("name % ?", text.value).preload_url_path.to_a
    end
    s
  end

  def new()
    search_companies.value = true
    search_brands.value = true;
    search_products.value = true;
  end
end

Regex searches have different operators but otherwise look just like the above: s.brands = BrandQuery.new.where("name ~ ?", text.value).preload_url_path.to_a. The ~ operator is case-sensitive, and the ~* operator is case-insensitive. Regex searches should not be made available to untrusted users, as an expression that uses arbitrary time and memory can be crafted.