Re: Using FULLTEXT search with different weights for various fields

The simple answer is this:

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(
setweight(to_tsvector(title), 'A') ||
setweight(to_tsvector(coalesce(description, '')), 'B') ||
setweight(to_tsvector(coalesce(steps, '')), 'C')
,
query
) as rank
FROM Recipes R,
plainto_tsquery('cookies eggs') query
WHERE
vector @@ query
ORDER BY rank desc LIMIT 100;

In the end I declared a function. Then you get:

CREATE INDEX ... ON f(title, description, steps);

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

On Thu, Aug 26, 2010 at 10:44 AM, Mike Christensen <mike(at)kitchenpc(dot)com>wrote:

> 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>