Message-ID: <001301c2b4a6$f969b360$36bd10ac@walrus>
From: "Peter Galbavy" <peter.galbavy@knowtion.net>
To: <pgsql-sql@postgresql.org>
Subject: weighting (the results of) a query ?
Date: Sun, 5 Jan 2003 10:41:15 -0000
Organization: Knowtion Ltd.
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

I have a table with a primary key ('md5') and a bunch of text fields.
There is one row per 'photograph' and the number of rows is about 1100
now but will rise to over 20,000 in a few months - assuming I get time
to import all my stuff.

I would like to offer users on my web site a free text search on these
text fields, but I would like to weight the results base on which field
the text came from.

Let's say those fields are (for simplicity) 'category', 'subcategory',
'caption' and 'keywords'.

I want to do:

    SELECT md5, weighting() FROM images WHERE
        category ~* 'term' OR subcategory ~* 'term' OR ...

Is there anything I can do - including writing functions - to return a
number that is somehow representative of which WHERE clause matched
'first' and even better the more columns matched ?

I am guessing that like 'C' an 'OR' conditional stops at the first match
and does not process further conditions after a previous one has
matched - that's good enough for me for day one...

It is not critial that I get a value out, the return order of results
could be fine too.

I would like to minimise the number of queries to the DB, but I can fall
back on doing one query per column and combining the results in perl.
This is my approach for an initial implementation later today unless
anyone can suggest otherwise...

Any pointers, tips, code, suggestions greatly appreciated.

Happy New Year all, BTW
--
Peter