global_fuzzy_search.sql

global_fuzzy_search.sql

-- global fuzzy search
-- Search for customers matching the search term in customer details
select
   qcs."name",
   qcs."photo",
   qcs."customer_id",
   -- fetch all contact methods for the customer that match the search query
   -- * contact method term display (national number for phone, email address for email)
   array_agg(qcs."term_display"),
   -- type of contact method (Email of Phone)
   array_agg(qcs."contact_method"),
   -- rank document column for (customer, contactable) record for query using cover density
   -- * max(ranks) = rank_score for the (customer, agg(contactables) record
   max(ts_rank_cd(qcs."document", to_tsquery('john:*'))) as rank_score,
   -- number that indicates how similar term is with query
   -- * max(similarities) = sim_score for the (customer, agg(contactables) record
   max(similarity(qcs."term", 'john:*')) as sim_score
from "quick_customer_search" as qcs
-- filter records by:
where (
   -- * not deleted
   qcs."deleted_at" is null
   -- AND
   and (
   -- ** document (computed from name + contact method) is matching the query
   qcs."document" @@ to_tsquery('john:*')
   -- *** OR term (contact method) similarity to query is greater than threshold
   or qcs."term" % 'john'
   -- ** or customer name similarity to query is greater than threshold
   or qcs."name" % 'john'
   )
)
-- aggregate together all contactables per customer and compute data based on them
group by qcs."customer_id", qcs."name", qcs."photo"
-- show first highest cover density, for equal cover density show first the highest similarity
order by rank_score desc, sim_score desc
limit 10;