CODEWITHSUNDEEP
phpsqlsearchsphinx
ServAce85
ServAce85

Reputation: 1622

Weigh Sphinx Results By Date

I am using Sphinx Search on a website to keep track of various table entries. These entries contain a date component and are less relevant after their data has been passed.

How can I have Sphinx assign more weight to results that match the search criteria (e.g. keyword search) and then assign less weight to those results that have a date in the past but still meet the search criteria? The illustration below should help to convey my needs:

Table containing data to be searched:

--------- ------------------ ---------
entry_id | title            | date    |
--------- ------------------ ---------
1         Tennis Racquet     12-10-2010
2         Basketball         03-24-2011
3         Tennis             03-03-2012
4         Skydiving          09-16-2012
5         Fishing            11-27-2012
6         Tennis Court       02-09-2013

Search Term:

tennis

Search Results:

  1. Entry 3 - Tennis
  2. Entry 6 - Tennis Court
  3. Entry 1 - Tennis Racquet - This is returned last because the date of this entry is before today's date (2-9-2012), but it still matches the search term

Upvotes: 0

Views: 2487

Answers (2)

ServAce85
ServAce85

Reputation: 1622

I have found a way to do this using a variation of @mobius' SetSortMode example.

Instead of using SPH_SORT_EXTENDED, I used SPH_SORT_EXPR along with the expression below:

$cl->SetSortMode ( SPH_SORT_EXPR, 
        " @weight + ( -1000000000/(" . time() . " - course_date_ts)) ");

This allows me to weigh upcoming results higher, then future results slightly lower, and finally old results last.

The large number in the numerator is to correspond with the number of decimal places in the UNIX timestamp.

Upvotes: 4

mobius
mobius

Reputation: 5174

On your sphinx.conf you should specify the date as a timestamp in your select query as such:

SELECT entry_id, title, UNIX_TIMESTAMP(date) as date_timestamp FROM Table;

and specify that the date_timestamp column is of type timestamp by:

sql_attr_timestamp = date_timestamp

Now you can sort the results by the timestamp column. Check the Sorting section of the Sphinx manual http://sphinxsearch.com/docs/current.html#sorting-modes

i.e in PHP:

$search->SetSortMode( SPH_SORT_EXTENDED, '@weight DESC, date_timestamp ASC );

Upvotes: 1

Related Questions