Search code examples
phpsearchfull-text-searchsphinx

Sphinx custom sorting (FIELD)

I, have table:

id | geo_id | values ....
 1     1      wvrevev
 2     2      wvrevev
 3     3      wvrevev
 3     4      wvrevev
 7     5      wvrevev
 8     6      wvrevev
 9     8      wvrevev

In the two fields sphinx indexes: id and geo_id

I need some sort, that would have been at the top of my specified records. In SQL receives such a request:

SELECT * FROM table ORDER BY FIELD(geo_id, "2", "3", "6") DESC

How do I specify sorting in Sphinx? This entry:

$cl->SetSortMode(SPH_SORT_EXTENDED, "FIELD(geo_id, "2", "3", "6") DESC");

Throws an error:

enter code hereindex sphinxoffers: sort-by attribute 'field' not found

How to get out of the situation?

Solution
    1. Sphinx doesnt have a FIELD() function
    2. You cant specify functions in SORT_EXTENDED mode. there is Sort Expression, but wont help due to 1.

    If you always use that list, can just create it as a attribute at indexing time... 

    sql_query = SELECT id, geo_id, FIELD(geo_id, "2", "3", "6") AS sorter, ....

    then in sphinxAPI code... 

    $cl->SetSortMode(SPH_SORT_EXTENDED, "sorter DESC");

    [Edit] or (to add information from the comments)...

    Can build the same logic as FIELD out of nested IF() functions. Can probaly use EXPRESSION sorting, or just create a virtual attribute with SetSelect, and sort by that (using EXTENDED)

    [Edit2] to use that new attribute, in sphinxQL, its just another sorting attribute used in ORDER BY (sphinxQL is in effect JUST SPH_SORT_EXTENDED sorting)... 

    SELECT * FROM index WHERE MATCH(...) ORDER BY sorter DESC, WEIGHT() DESC

    sort of thing.