How to get best matching products by number of matches in postgres

Question

Postgres 9.1 shopping cart contains product table

create table products (
  id char(30) primary key,
  name char(50),
  description text );

Cart has search field. If something is entered into it, autocomplete dropdown must show best matching products ordered by number products matched to this criteria.

How to implement such query in postgres 9.1 ? Search should performed by name and description fields in products table. It is sufficient to use substring match. Full text search with sophisticated text match is not strictly required.

Update

Word joote can be part of product name or description.

For example for first match in image text may contain

.. See on jootetina ..

and for other product

Kasutatakse jootetina tegemiseks ..

and another with upper case

Jootetina on see ..

In this case query should return word jootetina and matching count 3. How to make it working like auotcomplete which happens when search term is typed in Google Chrome address bar ?

How to implement this ?

Or if this is difficult, how to return word jootetina form all those texts which matches search term joote ?

What if it matches both name and description? Will it count as 2 or just one? — Clodoaldo Neto, Sep 29 '16 at 20:16
List shows number of products. If this is same product, it should counted only by one — Andrus, Sep 30 '16 at 06:05

Clodoaldo Neto · Accepted Answer · 2016-09-30T13:46:59.760

1

select word, count(distinct id) as total
from (
    select id,
        regexp_split_to_table(name || ' ' || description, E'\\s+') as word
    from products
) s
where position(lower('joote') in lower(word)) > 0
group by word
order by 2 desc, 1

edited Sep 30 '16 at 13:46

answered Sep 29 '16 at 20:34

Clodoaldo Neto

118,695
26
233
260

This query does not return matching words inside text. How to make it work more like real search, like autocomplete when something is typed in chrome toolbar? I updated question and provided sample. – Andrus Sep 30 '16 at 06:13
There is related question in http://stackoverflow.com/questions/39790930/how-to-get-best-matching-words-from-name-and-description – Andrus Sep 30 '16 at 11:53

Erwin Brandstetter · Answer 2 · 2023-08-01T19:47:49.953

First of all, do not use the data type char(n). You want varchar(n) or just text. I suggest text. See:

Any downsides of using data type "text" for storing strings?

With that fixed, you need a smart index-based approach or this is a performance nightmare. Either trigram GIN indexes on the original columns or a text_pattern_ops btree index on a materialized view of individual words (with count).

Pattern matching with LIKE, SIMILAR TO or regular expressions

The MV approach is probably superior for many repetitions among words.

How to get best matching products by number of matches in postgres

2 Answers2