Right now I'm using Sphinx to make text search over some entities in my database. For simplicity lets asume that these entities are Clients with a name, description and a role field.
I do text search over name with weight 200 and over the description with weight 50 on matches on those fields. Everything works as expected. The problem is that right now I need to order by role, leaving role 1 always first.
On a normal query I would do something like
... ORDER BY c.role DESC, c.sphinx_ranking DESC
Is this possible using only sphinx? I know that is posible to sort by a filter but i don't know if is possible to use both, the text weight order AND the field role.
I found the answer reading Sphinx documentation. If someone has the same problem here is the solution:
To mix both, the weight from sphinx and a field that works as a filter you must use SPH_SORT_EXTENDED as Sort Mode and as parameter you pass the fields like in a SQL statement.
On my case, I'm using php so the code is this:
$sphinxclient->SetSortMode(SPH_SORT_EXTENDED,"role DESC,@weight DESC");
Where @weight is the Sphinx Ranking value and role is a field on my table. With this I get the clients whose role is 1 first and then the result is sorted by sphinx text rating. Very Simple!