CODEWITHSUNDEEP
sqlpostgresqlfull-text-search
Lee Eather
Lee Eather

Reputation: 355

How come my float value is always returning 0 for all entries in postgres query?

Ive been wanting to incorporate a full-text search in to my website but getting the ranking of return results from a search is holding me up. The docs show how to do it but I keep getting 0 as a result for all my post entries selected.

my_app_development=# SELECT content, ts_rank(to_tsvector('microposts.content'), query) 
AS rank FROM microposts, to_tsquery('sit') query WHERE microposts.content @@ query 
ORDER BY rank DESC LIMIT 10;
                      content                      | rank
---------------------------------------------------+------
 Dolorem sed omnis iusto sit inventore quia dolor. |    0
 Dolorem sed omnis iusto sit inventore quia dolor. |    0
 Dolorem sed omnis iusto sit inventore quia dolor. |    0
 Dolorem sed omnis iusto sit inventore quia dolor. |    0
 Dolorem sed omnis iusto sit inventore quia dolor. |    0
 Vel sit ut qui aperiam aut sunt.                  |    0
 Vel sit ut qui aperiam aut sunt.                  |    0
 Vel sit ut qui aperiam aut sunt.                  |    0
 Vel sit ut qui aperiam aut sunt.                  |    0
 Dolorem sed omnis iusto sit inventore quia dolor. |    0
 (10 rows)

Is this expected behavior and if so what am I doing wrong.

Cheers.

Lee.

Upvotes: 1

Views: 313

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51559

you are looking rate for same phrase in every row to_tsvector('microposts.content'), instead you should use ts_rank(to_tsvector(content),query)

also, I assume you should define language for accurate results:

SELECT content, ts_rank(to_tsvector('latin',content), query) 
AS rank FROM microposts, to_tsquery('latin','sit') query WHERE microposts.content @@ query 
ORDER BY rank DESC LIMIT 10;

also I'm not sure whether sit is not a stop word to be ignored at all...

Upvotes: 1

Related Questions