Ryan Wallace wrote:
> Richard Huxton wrote:
>> Failing that, where I've had many (a dozen) different sources but want
>> to search them all I've built a textsearch_blocks table with columns to
>> identify the source and have triggers that keep it up to date.
>
> Once you've built the text search blocks table, how do you search it? Do you
> perform
> twelve separate queries or can you just do one?
OK, you have a table something like:
fulltext_blocks ( section varchar(32), itemid int4, words tsvector,them PRIMARY KEY (section, itemid)
)
Now assume two of the things I search are "news" and "faqs". I'm
assuming they've both got a simple serial pkey - if not, "itemid" above
needs to be text and you'll have to cast.
For each target table (news, faqs) add a trigger that updates
fulltext_blocks appropriately. This can include weighting title and body
of a news article.
Then, search the fulltext_blocks table, optionally filtering by section.
If you're going to have lots of results put the ids into a (perhapd
temporary) results-table. Then join your results back to the original
tables with the appropriate UNION (if you need to - it might be you
fetch results one at a time elsewhere in your app).
SELECT n.id, n.title, n.body
FROM news n JOIN results r ON n.id=r.id
WHERE r.section='news'
UNION ALL
SELECT f.id, f,question, f.answer
FROM faqs f JOIN results r ON f.id=r.id
WHERE r.section='faqs'
;
You'll probably want to set ownership/permissions on the triggers /
fulltext_blocks table so you can't accidentally update it directly.
In mine I even had a "documents" section which relied on an external
cron-driven script to strip the first 32k of text out of uploaded
documents (pdf,word) in addition to user-supplied metadata (title, summary).
Note - this is basically simulating what we could do if you could index
a view. The fulltext_blocks table is nothing more than a materialised view.
HTH
-- Richard Huxton Archonet Ltd
