Organization suggest: elasticsearch really needed?

[nicolasfranck]

Search in elasticsearch now done as follows:

• fields id, name_dut, name_eng, other_id.ugent_id, other_id.ugent_memorialis_id and other_id.biblio_id are copied to a "catch all field" all
• client splits input string on whitespace, and executes a wildcard search on each part: BI becomes all:BI*

So search is rather simpel, so could "easily" be converted to a sql statement:

SELECT * FROM organization WHERE id ILIKE 'BI%' OR name_dut ILIKE 'BI%' OR name_eng ILIKE 'BI%'

but I am excluding the jsonb queries, which may become very difficult, and which probably have no LIKE available (WHERE other_id @> '[{"id":"BI"}]' is too specific)

[nicolasfranck]

Ranking results:

select id, full_name, ts_rank_cd(ts, tsq) as tsr from person, to_tsquery('nico:* & fra:*') tsq where ts @@ tsq order by tsr asc;

cf. https://leandronsp.com/a-powerful-full-text-search-in-postgresql-in-less-than-20-lines

But that cannot be done in entgo, which only allows you to add statements to the "where" clause, not to the "from" clause.

Compare (without ranking):

authority=# select id, full_name as tsr from person where ts @@ to_tsquery('nico:* & fra:*');
   id   |       tsr        
--------+------------------
  77059 | Francesco Nicoli
 195503 | Nico Fransaert
      1 | Nicolas Franck

with ranking:

authority=# select id, full_name, ts_rank_cd(ts, tsq) as tsr from person, to_tsquery('nico:* & fra:*') tsq where ts @@ tsq order by tsr asc;
   id   |    full_name     | tsr 
--------+------------------+-----
 195503 | Nico Fransaert   | 0.1
      1 | Nicolas Franck   | 0.1
  77059 | Francesco Nicoli | 0.1