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 ?

Answer 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