Обсуждение: How to perform full text search
User can enter any number of words as search string.
In shopping cart the following query is used to find products,
eq. if "red cat" is entered:
select * from products
where productname ilike '%'||'red cat'||'%'
or productdescription ilike '%'||'red cat'||'%'
limit 100
This does not find products like "red or black cat".
How to change this query so that it returns 100 best matches for for given search string?
I read documentaton about full text search but havent found step by step solution for this.
Using PostgreSQL 8.2.7 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.1.2 20061115 (prerelease) (Debian 4.1.1-21)
Should I install some contribs to 8.2 or is it better to upgrade server ?
Where to find step by step instructions making this work ?
Andrus.
>Parse the entry string into words (aka tokens) and assemble with the and >operator. E.g. 'red cat' becomes 'red & cat'. >Then add vector; more info >in articles I provide links to later in this note. >WHERE to_tsvector ( productname || ' ' || productdescription ) @@ >to_tsquery ( 'red & cat' ) Since there were no responces for a while, I went with another solution. Splitted search string to words like you but converted query to select +case when productname ilike '%red%' then 2 else 0 end +case when productdescription ilike '%red%' then 1 else 0 end +case when productname ilike '%cat%' then 1.7 else 0 end +case when productdescription ilike '%cat%' then 0.7 else 0 end from products order by 1 desc limit 100 This allows to define relevance. Is my solution reasonable ? Andrus
On 03/18/12 11:45 AM, Andrus wrote: > select > +case when productname ilike '%red%' then 2 else 0 end > +case when productdescription ilike '%red%' then 1 else 0 end > +case when productname ilike '%cat%' then 1.7 else 0 end > +case when productdescription ilike '%cat%' then 0.7 else 0 end > from products > order by 1 desc > limit 100 > > This allows to define relevance. > Is my solution reasonable ? if you don't mind a full table sequential scan each time you execute that, I suppose. -- john r pierce N 37, W 122 santa cruz ca mid-left coast