Search Postgresql Archives

Re: Ranking search results using multiple fields in PostgreSQL fulltext search

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Ivan,
If i create a tsvector as you mentioned with concatenation operator, my search query will search in any of these fields which are concatenated in my tsvector.
For example, if i create tsvector like this,
UPDATE document_table SET search_col = 
  setweight(to_tsvector(coalesce(title,'')), 'A') ||
  setweight(to_tsvector(coalesce(summary,'')), 'B'));

and do a query like this
select title, ts_rank(search_col, to_tsquery('this is my text search') AS rank
FROM search_col @@ to_tsvector('this & is & my & text & search')
ORDER BY rank DESC
the above query will search in title and summary and will give me the results. But i dont want in that way.When a user wants to search in title, it should just search in title but the results should be ranked based on title and summary field.
On Mon, Oct 12, 2009 at 7:16 PM, Ivan Sergio Borgonovo <mail@xxxxxxxxxxxxxxx> wrote:
On Mon, 12 Oct 2009 18:46:02 +0530
Gaini Rajeshwar <raja.rajeshwar2006@xxxxxxxxx> wrote:

> Hi,
> is there a way to rank the search results based on multiple fields
> in postgreSQL?
> For example,
> i have *title*, *abstract*, *summary*, *body* as fields/columns in
> my database. When user searches on *title*, i want to rank the
> results based on *title* field as well as *summary* field, where
> importance(summary) > importance(title). But the results should be
> exactly matching the terms in "title" rather than "title" OR
> "summary"

http://www.postgresql.org/docs/current/interactive/textsearch-controls.html

Basically, as you can read in the docs:
- you create a ts_vector concatenating and giving a weight the
 various fields.
- then you compare your ts_vector with
 plainto_tsquery(config, yourinput) @@
 yourpreviouslycomputedts_vector

and order by ts_rank(yourpreviouslycomputedts_vector, yourinput)
(or ts_rank_cd)


--
Ivan Sergio Borgonovo
http://www.webthatworks.it



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux