Using FULLTEXT search with different weights for various fields
От | Mike Christensen |
---|---|
Тема | Using FULLTEXT search with different weights for various fields |
Дата | |
Msg-id | AANLkTinxT+Zb5V=OuUNS0UM6v4XMM1_Zr78oUBqq1HCW@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: Using FULLTEXT search with different weights for
various fields
|
Список | 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
В списке pgsql-general по дате отправления: