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
Message-ID: <001301c2b4a6$f969b360$36bd10ac@walrus> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: peter.galbavy@knowtion.net
Subject: Re: weighting (the results of) a query ?
In-Reply-To: <001301c2b4a6$f969b360$36bd10ac@walrus>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox