esqueleto-textsearch: PostgreSQL full text search for Esqueleto

[ database, library, mit ] [ Propose Tags ]

PostgreSQL text search functions for Esqueleto.


[Skip to Readme]

Downloads

Maintainer's Corner

Package maintainers

For package maintainers and hackage trustees

Candidates

Versions [RSS] 1.0.0.3, 1.1.0, 1.1.1, 1.1.2, 1.1.3, 1.1.4
Change log Changelog.md
Dependencies base (>=4.9 && <5), esqueleto (>=3.2 && <3.6), parsec (<3.2), persistent (>=2.8.2 && <2.15), persistent-postgresql (>=2.10 && <2.15), text (>=1.2 && <2.2) [details]
License MIT
Copyright 2023 Jappie Klooster <hi@jappie.me>, 2015 Alberto Valverde González
Author Jappie Klooster <hi@jappie.me>, Alberto Valverde González
Maintainer hi@jappie.me
Category Database
Home page https://github.com/jappeace/esqueleto-textsearch
Source repo head: git clone https://github.com/jappeace/esqueleto-textsearch
Uploaded by Jappie at 2024-02-17T12:14:05Z
Distributions
Downloads 92 total (70 in the last 30 days)
Rating (no votes yet) [estimated by Bayesian average]
Your Rating
  • λ
  • λ
  • λ
Status Docs available [build log]
Last success reported on 2024-02-17 [all 1 reports]

Readme for esqueleto-textsearch-1.1.4

[back to package description]

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:
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 describes better what goes on here.

  1. Create an associated persistent model to convince esqueleto this is a table
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
  1. make sure to periodically refresh this view, or setup database triggers to do that for you.
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();
  1. use this library to search, for example:
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)]