Rails custom search using postgresql advice

Question

I would love some advice on what's the best direction to take with the following search criteria I need to implement in my passion project app:

• search field will take in a string, in this case it will be a question
• in my database i will have, as an example, 100 pre-written answers with title:string answer:text and tag:string
• the search will compare at least three keywords from the question, to three answer tags
• the search result will be displayed on the answers#index page and show only one answer

Would it be best to build something in jQuery that converts the :tag string into an array of tags? This way I could compare against indexes ...

I tried using ElasticSearch and spent around 5 hours trying to get it installed with no luck.

Thanks!

Answer 1

If the primary search matching criterion is show answer that has three or more of the search terms as tags, then this will be a little tricky to cleanly implement with Postgres.

There are a couple routes you could go, neither of which will execute as fast as an Elasticsearch query:

1. SQL Subquery on an AnswerTags table:

   Something like:

   SELECT answer_id

   FROM answer_tags

   WHERE tag_text IN (<list of query terms>)

   GROUP BY answer_id HAVING COUNT(*) >= 3

   ORDER BY COUNT(*) desc

2. Use a Postgres-specific array type to keep all Tags in an Answer field. Write a stored procedure to calculate tag intersection/overlap:

   Some suggested approaches in this thread: Postgres - Function to return the intersection of 2 ARRAYs?

If you pursue Elasticsearch as a solution, you'll have much better performance (though if you only have 100 records, this may not be significant), and a more straightforward implementation path. (See the minimum_should_match parameter associated with the match query type).

Elasticsearch also enables a lot more nuance in your matching logic and search features (e.g. tiebreaking cases where multiple records match, autosuggesting answer titles or tags, synonyms/stemming and other text normalization methods)

Elastic recently acquired a great Elasticsearch hosting company Found.no which is a good choice if you want to bypass installation/configuration tasks to get up and running immediately. They offer a 14-day free trial, and Found being owned by Elastic, upgrades faster than any other ES hosts and includes premium plugins with each cluster instance.