People suggest: elasticsearch really needed?

[nicolasfranck]

Search in elasticsearch now done as follows:

• fields first_name, last_name and full_name are copied into a global field all at index time
• client splits input string on whitespace, and executes a wildcard search on each part: nico fra becomes all:nico* AND all:fra*

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

SELECT * FROM person WHERE (first_name ILIKE 'nico%' OR last_name ILIKE 'nico%' OR full_name ILIKE 'nico%') AND (first_name ILIKE 'fra%' OR last_name ILIKE 'fra%' OR full_name ILIKE 'fra%')

Drawbacks:

• the more fields we want to include in our search, the longer the sql query
• no way to influence score. e.g. if we want to score a match on field preferred_first_name

[netsensei]

As an alternative to a simple LIKE statement, consider using PostgreSQL's full-text search capabilities via tsquery and tsvector:

• https://www.crunchydata.com/blog/postgres-full-text-search-a-search-engine-in-a-database
• https://supabase.com/blog/postgres-full-text-search-vs-the-rest

[nics]

@nicolasfranck good intro https://supabase.com/blog/postgres-full-text-search-vs-the-rest

[nicolasfranck]

add column with type simple to disable language specific stemming (e.g. Nicolas becomes nicola in English stemming):

alter table person add column ts tsvector generated always as (to_tsvector('simple',full_name)) stored;

alter table organization add column ts tsvector generated always as (to_tsvector('simple', jsonb_path_query_array(other_id, '$[*].id'))) stored;

with prepared statements and placeholders (safe against sql insertion) and prefixing:

prepare se(text,text) as select full_name from person where ts @@ to_tsquery('simple',$1 || ':* & ' || $2 || ':*');
execute se('nicol', 'fra');

[nicolasfranck]

Branch https://github.com/ugent-library/people/tree/ts available.

Important note:

• I did not find a way in entgo to automatically create the generated columns, so for now you'll have to start the application (which creates the table), and then execute the alter-table-statements from the previous comment.
• the weird code that generates a "ts query" is one way to convert a query like nico fra into to_tsquery('simple', 'nico:* & fra:*') with protection against sql injection

[nics]

@nicolasfranck you can ask the question to the ent team?

[nicolasfranck]

@nics https://github.com/ent/ent/issues/3168

Maybe I'll add this as a hook, or on startup. You can always do a raw Exec

[nicolasfranck]

@nics a bit ugly, but I add those fields at startup: https://github.com/ugent-library/people/commit/60e49dd555766f03f4c3c9acbb40233e32980eb6

[nicolasfranck]

and now also with an index using GIN (not a fan though ;-) )

[nicolasfranck]

There are two ways to create the extra columns:

• execute a custom query after connecting to the database and having run the migration
• add an "apply hook" to the schema migration. https://entgo.io/docs/migrate/#apply-hook-example