From: Peter Galbavy <peter.galbavy@knowtion.net>
To: pgsql-sql@postgresql.org
Subject: Re: weighting (the results of) a query ?
Date: Tue, 7 Jan 2003 16:04:28 -0000
Message-ID: <021001c2b666$752c1d60$4528a8c0@cblan.mblox.com> (raw)
References: <001301c2b4a6$f969b360$36bd10ac@walrus>
Thanks to Len Morgan for the hints to get to this:
SELECT *, '4' as result FROM images
WHERE
iptc_release_date < 'today' AND
iptc_priority BETWEEN 1 AND 5 AND iptc_caption ~* 'term'
UNION
SELECT *, '3' as result FROM images
WHERE
iptc_release_date < 'today' AND
iptc_priority BETWEEN 1 AND 5 AND iptc_keywords ~* 'term'
UNION
SELECT *, '2' as result FROM images
WHERE
iptc_release_date < 'today' AND
iptc_priority BETWEEN 1 AND 5 AND iptc_category ~* 'term'
UNION
SELECT *, '1' as result FROM images
WHERE
iptc_release_date < 'today' AND
iptc_priority BETWEEN 1 AND 5 AND iptc_subcategory ~* 'term'
ORDER BY result desc, iptc_priority asc, shot_time, image_serial asc;
Using a constant and UNION made it work OK. Not sure yet on real world
performance, but that's what tuning is for :)
Hope someone finds this in the archive and finds it useful.
Peter
----- Original Message -----
From: "Peter Galbavy" <peter.galbavy@knowtion.net>
To: <pgsql-sql@postgresql.org>
Sent: Sunday, January 05, 2003 10:41 AM
Subject: [SQL] weighting (the results of) a query ?
> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
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: <021001c2b666$752c1d60$4528a8c0@cblan.mblox.com>
* 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