1

i need some help. site has 2 sort types: by relevancy and date. Sometimes happends that issues with most high score are to old, and the newest has small score. So needed some common query based on 2 marks.

Relecancy query looks like 'ORDER BY' ts_rank(EXTENDED_INDEX, custom_tsquery('english', 'test', 0))'

and second one just 'ORDER BY table.date'

Are any ideas how improve search? Maybe some second ts_rank by date?

Dmitry S
  • 4,990
  • 2
  • 24
  • 32
Alexander Shavelev
  • 324
  • 1
  • 12

1 Answers1

3

Based on the question it's unclear what dataset you are using as an example, but you can basically use ORDER BY rank DESC,date DESC in your query, so you will have most "recent" and highly "ranked" at the top of your result set.

WITH t(id,t,d) AS ( VALUES
  (1,to_tsvector('english','one'),'2016-03-18'::DATE),
  (2,to_tsvector('english','two words'),'2016-03-17'::DATE),
  (3,to_tsvector('english','three words we are looking for'),'2016-03-16'::DATE),
  (4,to_tsvector('english','four words goes here'),'2016-03-15'::DATE)
)
SELECT
  id,
  ts_rank(t,q) AS rank,
  d
FROM t,to_tsquery('english','three | words') AS q
ORDER BY rank DESC NULLS LAST,d DESC;

Result :

 id |   rank    |     d      
----+-----------+------------
  3 | 0.0607927 | 2016-03-16
  2 | 0.0303964 | 2016-03-17
  4 | 0.0303964 | 2016-03-15
  1 |         0 | 2016-03-18
(4 rows)
Dmitry S
  • 4,990
  • 2
  • 24
  • 32
  • 3
    thanks for your answer, but we need bigger influence of date, so decided to choose such variant **ORDER BY ts_rank()^2/abs(date_difference+0.5)** where date_difference - days count between today and post date (maybe it will help somebody) – Alexander Shavelev Mar 23 '16 at 16:14