Date: Mon, 4 Nov 2002 16:53:20 -0600
From: "Ross J. Reedstrom" <reedstrm@rice.edu>
To: pgsql-sql@postgresql.org
Subject: counting text matches - any recipes?
Message-ID: <20021104225320.GA6001@wallace.ece.rice.edu>
Mail-Followup-To: "Ross J. Reedstrom" <reedstrm@ece.rice.edu>,
	pgsql-sql@postgresql.org
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
User-Agent: Mutt/1.4i

Hey all - 
I'm working on the query interface to a system that stores a bunch of
(realtively) small text documents, with titles and keywords, etc.

For the basic interface, we have the user types some words into a textbox,
then just search 'everything'. I'm weighting the results, so that hits
on the title count more than on keywords, which count more than on the
abstract, which count more than in the body.

This is working reasonably well, but I'm stuck on one counting problem:
what's the natural way to count multiple matches in SQL?

As an example, let's use the title a.k.a. 'name' of an article (called
modules, in the schema).

I've already generating a big select with a stanza for each class of match,
unioned together, weighted and summed. Here's a typical stanza for the name:

    select moduleid, name, version, created, revised, abstract, 
           count(*)*100 as weight
     from current_modules cm, abstracts a
     where
      cm.abstractid = a.abstractid
      and (
               name ~* 'Fourier'
           or  name ~* 'series'
       )
     group by  moduleid, name, version, created, revised, abstract 

Obviously, this will give one hit on a module with the name 'Fourier Series',
as well as one for 'Fourier Transforms', and one for 'Time Series Analysis'.

It's probably blindingly obvious, but how would I structure this to get
_two_ hits for 'Fourier Series', that'll still scale to, say, a dozen
search terms entered? I've thought of the subselect route, as so:

    select moduleid, name, version, created, revised, abstract, 
           count(*)*100 as weight
     from (
        select moduleid, abstractid, name, version, created, revised 
             from current_modules cm
             where name ~* 'Fourier'
        union all 
        select moduleid, abstractid, name, version, created, revised 
             from current_modules cm
             where name ~* 'series'
     ) as bar, abstracts a
     where
      bar.abstractid = a.abstractid
     group by  moduleid, name, version, created, revised, abstract 

But I'm not sure how well that'll scale, since this is already a subselect,
so I'd be nesting two deep.

Ross