Using FULLTEXT search with different weights for various fields

From: Mike Christensen <mike(at)kitchenpc(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Using FULLTEXT search with different weights for various fields
Date: 2010-08-26 08:44:46
Message-ID: AANLkTinxT+Zb5V=OuUNS0UM6v4XMM1_Zr78oUBqq1HCW@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I'm trying to make some improvements to my search results by taking
advantage of Postgres' setweight function, but am having a lot of
problems getting a query to run.. Here's the query that I run now
(I've removed some parts that just make it more complicated than you
need to be bothered with)..

SELECT R.RecipeId, R.Title, R.Description, R.ImageUrl, R.Rating FROM Recipes R
WHERE (to_tsvector('english', title || ' ' || coalesce(description,
'') || ' ' || coalesce(steps, '')) @@ plainto_tsquery(:search))
ORDER BY R.Rating DESC LIMIT 100;

:search will be something the user types in, such as "cookies eggs".
This runs well, since I have an index on that vector expression.
However, I'd like to do the following:

title should have a weight of A.

description should have a weight of B.

steps should have a weight of C.

I've tried a few things based on the documentation at
http://www.postgresql.org/docs/8.4/static/textsearch-controls.html,
but am not having a lot of luck. Here's what I've come up with some
far:

SELECT R.RecipeId, R.Title, R.Description, R.ImageUrl, R.Rating,
setweight(to_tsvector(title), 'A') ||
setweight(to_tsvector(coalesce(description, '')), 'B') ||
setweight(to_tsvector(coalesce(steps, '')), 'C') as vector,
ts_rank_cd(vector, query) as rank
FROM Recipes R,
plainto_tsquery('cookies eggs') query
WHERE
vector @@ query
ORDER BY rank desc LIMIT 100;

This doesn't work due to the error: column 'vector' does not exist,
which I guess is a valid point the way the query is parsed. The
examples basically assume you have a column called textsearch which is
a tsvector, and you use a trigger or something to update it. I'm
trying to avoid modifying my table schema for now. Is there a way to
express this query without pre-computing the tsvector on the table?
Also, is having a tsvector in the table basically the standard
approach and something I should just get used to doing? Maybe I can
use a view that computers the tsvector and index that? Sorry, this is
probably a totally brain dead fulltext question, but I'm new to this
whole concept. I make pretty web pages, and am not as smart as the
people on this list.

Mike

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Atul.Goel 2010-08-26 09:27:46 Re: IBATIS support for postgres cursors
Previous Message Wappler, Robert 2010-08-26 07:51:38 Re: Optimizing queries that use multiple tables and many order by columns