Right now I am using Sphinx to text search some objects in my database. For simplicity, it can be assumed that these objects are Clients with a name, description, and role field.
I do a text search by name with a weight of 200 and over a description with a weight of 50 on matches in these fields. Everything works as expected. The problem is that now I need to order by roles, leaving role 1 always the first.
In a normal query, I would do something like
... ORDER BY c.role DESC, c.sphinx_ranking DESC
Is it possible to use only the sphinx? I know that it is possible to sort by filter, but I do not know if both options can be used: the order of the weight of the text and the role of the field.
Solved: I found an answer reading the Sphinx documentation. If anyone has the same problem, here is the solution:
To mix both weight and a field that works as a filter, you must use SPH_SORT_EXTENDED as the sort mode, and you pass the fields as a parameter, as in the SQL statement.
In my case, I am using php, so the code is as follows:
$sphinxclient->SetSortMode(SPH_SORT_EXTENDED,"role DESC,@weight DESC");
Where @weight is the Sphinx rating value and the role is the field on my table. With this, I get clients whose role is 1 first, and then the result is sorted by sphinx text rating. Very simple!