Sphinx/PHP: Can Sphinx return matching order of a given array?

Question

Using Sphinx 2.0.6, is there a way to have sphinx return a specific order based on the document ID?

For example, say there are 1000 documents all having id 1-1000. But I want to return, in order, ID 999,1000,4,5,2,and so on.

This use case: The positioning is dynamic and needs to be done through Sphinx. The positioning value needs to be as an attribute that can change on-the-fly. This is also paged -- so I can't simply gather the ID Set and request a SQL. Sphinx itself needs to return the specific order I give it.

$cl->setSelect("*,FIND_IN_SET(id,".implode($id_array).") AS id_position");
$cl->SetSortMode(SPH_SORT_EXTENDED, 'id_position DESC');

$cl->setSelect("*,FIELD(id,".implode($id_array).") AS id_position");
$cl->SetSortMode(SPH_SORT_EXTENDED, 'id_position DESC');

Unfortunately, doesn't look like Sphinx supports FIELD() and FIELD_IN_SET().

Any ideas how to complete this task? I'm at a loss right now and could use the help!

Answer 1

Figured it out thankfully! This is an excellent solution to being able to order by a dynamic id array. (real world use... user's favorites, recommended products, user's most visited)

Sphinx PHP Code:

$cl->SetOverride("id_position", SPH_ATTR_INTEGER, user_id_position());
$cl->SetSortMode(SPH_SORT_EXTENDED, "id_position ASC");

PHP Function to create the associative array:

function user_id_position() {
        $id = $_original_id_list // This variable is the original id list that is in the correct order
        $max = count($id);
        $set = array();
        for ($i=0;$i < $max;$i++)
            $set[$id[$i]] = $i; // turns the array into array(document_id1 => position, document_id2 => position, ...)
        return $set;
}

sphinx.conf

source index_name
{
    sql_query                       = \
            SELECT \
                    *, 0 as id_position \
            FROM \
                    database_table;
    sql_attr_uint         = id_position
}

After adding the information to sphinx.conf you'll need to --rotate and it'll work