esqueleto-textsearch 1.1.0 → 1.1.1

raw patch · 3 files changed

+82/−4 lines, 3 filesPVP ok

version bump matches the API change (PVP)

API changes (from Hackage documentation)

Files

• Changelog.md +3/−0
• README.md +76/−1
• esqueleto-textsearch.cabal +3/−3

@@ -1,5 +1,8 @@ # Change log for esqueleto text search  +## Version 1.1.1++ add tutorial in the readme+ ## Version 1.1.0  + Removed weird patch numbering

@@ -1,7 +1,82 @@-# esqueleto-fts+# esqueleto-textsearch Haskell bindings for postgres full text search in esqueleto. for a good explenation see https://rachbelaid.com/postgres-full-text-search-is-good-enough/  you can turn postgres into a database that is similar in performance for search as elastic search, without having  to deal with elastic search.++## Tutorial++1. decide which fields you want to search for,+   this can be from several tables+2. setup a materialized view with the fields, for example:+```sql+CREATE MATERIALIZED VIEW public.companies_search_index AS+ SELECT c.id,+    c.name,+    c.type,+    c.trade,+    countries.name AS domicile,+    p.name AS parent,+    c.is_archived,+    COALESCE(string_agg((cd.name)::text, ', '::text), ''::text) AS domains,+    (((((setweight(to_tsvector((c.name)::text), 'A'::"char") || setweight(to_tsvector((COALESCE(c.type, ''::character varying))::text), 'D'::"char")) || setweight(to_tsvector((c.trade)::text), 'D'::"char")) || setweight(to_tsvector((COALESCE(countries.name, ''::character varying))::text), 'C'::"char")) || setweight(to_tsvector((COALESCE(p.name, ''::character varying))::text), 'B'::"char")) || setweight(to_tsvector(COALESCE(string_agg((cd.name)::text, ' '::text), ''::text)), 'A'::"char")) AS document+   FROM (((public.companies c+     LEFT JOIN public.countries ON ((countries.id = c.domicile)))+     LEFT JOIN public.companies p ON ((p.id = c.parent)))+     LEFT JOIN public.company_domains cd ON ((cd.company_id = c.id)))+  GROUP BY c.id, p.parent, c.name, c.type, c.trade, countries.name, p.name+  WITH NO DATA;+```+the [blogpost](https://rachbelaid.com/postgres-full-text-search-is-good-enough/) describes better what goes on here.++3. Create an associated persistent model to convince esqueleto this is a table++```haskell+mkPersistWith sqlSettings $(discoverEntities) [persistLowerCase|+CompanySearchIndex sql=companies_search_index+  Id           CompanyId -- in this case a 1 to 1 relation with the real company table+  -- these fields are mostly for debugging the document+  name         Text+  type         Text Maybe+  trade        Text+  domicile     Text Maybe+  parent       Text Maybe+  domains      Text+  -- we query based on the documeent, this is the important bit.+  document     TsVector+  isArchived   Bool+  deriving     Show+|]++instance ToBaseId CompanySearchIndex where+  type BaseEnt CompanySearchIndex = Company+  toBaseIdWitness = CompanySearchIndexKey+```+4. make sure to periodically refresh this view, or setup database triggers to do that for you.+```sql+CREATE FUNCTION public.refresh_companies_search_index() RETURNS trigger+    LANGUAGE plpgsql+    AS $$+begin+    refresh materialized view companies_search_index;+    return null;+end $$;++CREATE TRIGGER refresh_companies_search_index AFTER INSERT OR DELETE OR UPDATE OR TRUNCATE ON public.companies FOR EACH STATEMENT EXECUTE FUNCTION public.refresh_companies_search_index();+```+5. use this library to search, for example:+```haskell+import Database.Esqueleto.TextSearch.Language(SearchTerm, prefixAndQuery, ((@@.)), ts_rank)+import Database.Esqueleto.TextSearch.Types(defaultWeights)++searchCompany :: SqlExpr (Entity CompanySearchIndex) -> SearchTerm -> SqlQuery ()+searchCompany company term = do+  let query = prefixAndQuery term+      norm = val []+  where_ $ (company ^. CompanySearchIndexDocument) @@. query+  orderBy [desc (ts_rank (val defaultWeights)+                 (company ^. CompanySearchIndexDocument)+                 query norm)]+```

@@ -1,12 +1,12 @@ name:               esqueleto-textsearch-version:            1.1.0+version:            1.1.1 synopsis:           PostgreSQL full text search for Esqueleto description:        PostgreSQL text search functions for Esqueleto.-homepage:           https://github.com/jappeace/esqueleto-textsearch+homepage:           https://github.com/SupercedeTech/esqueleto-textsearch-ii license:            MIT license-file:       LICENSE author:             Alberto Valverde González-maintainer:         jappie, hi@jappie.me+maintainer:         info@supercede.com copyright:          2015 Alberto Valverde González category:           Database build-type:         Simple