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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
