0

Shopping cart uses following query for searching products:

select ...
from toode,yksus7,artliik,artryhm
where toode.nimetus ilike '%'||{{5}}||'%' escape '!' 
      or toode.toode ilike '%'||{{5}}||'%' escape '!' 
      or toode.analoog ilike '%'||{{5}}||'%' escape '!' 
      or yksus7.nimetus ilike '%'||{{5}}||'%' escape '!' 
      or artliik.artlnimi ilike '%'||{{5}}||'%' escape '!' 
      or artryhm.nimetus ilike '%'||{{5}}||'%' escape '!' 
      or toode.markused ilike '%'||{{5}}||'%' escape '!'
...

search field types are character(n) and TEXT {{5}} represents search term entered by user into search box in web page.

This query words only exact matches. Texts from diffrent tables toode,yksus7,artliik,artryhm joined in WHERE clause needs searched and best matching results returned.

If User types 

blank pencil

query does not found records containing text with words not ix exact order like

pencil black

How to fix this so that similar results are also returned ? PostgreSql versions starting from 9.1 are used.

Andrus
  • 26,339
  • 60
  • 204
  • 378
  • There is pretty good documentation about full text search [here](https://www.postgresql.org/docs/current/static/textsearch.html). – Laurenz Albe Sep 22 '17 at 13:23
  • I used `WHERE to_tsvector('english',toode.nimetus) @@ array_to_string((string_to_array({{5}}, ' '))[1:99], ' & ') ::tsquery or ...` is this OK ? – Andrus Sep 22 '17 at 14:19
  • `plainto_tsquery` is simpler. Of course queries with `OR` are never efficient; consider using `UNION`. – Laurenz Albe Sep 22 '17 at 14:34
  • I changed it to `to_tsvector('english',toode.nimetus) @@ plainto_tsquery({{8}})` and created index `create index toode_nimetus_fts_idx on toode USING gin(to_tsvector('english', nimetus))` . Is this OK. Will this index used for search – Andrus Sep 22 '17 at 18:08
  • Should work. Try with `EXPLAIN` to be sure. – Laurenz Albe Sep 23 '17 at 03:04

0 Answers0