Sphinx Sorting & Grouping

Question

Sphinx is being used to search for keywords in product names.

Ranking Mode: SPH_RANK_SPH04

Sort Mode: SPH_SORT_RELEVANCE

The results that are returned are relevant, and all contain the requested keywords.

Is there a way to apply additional filtering to place products with the same name (they are multi-colored variants) next to each other?

Answer 1

Well there is a GROUP BY feature in sphinx. The problem is you sound like you want multiple items per group (ie per item title) - which is only available via SphinxQL (sounds like you using SphinxAPI).

Now might be a good point to convert to SphinxQL :)

Something like

 sphinxQL> SELECT id,title,MAX(WEIGHT()) AS tpw FROM sample2 
   WHERE MATCH('keyword') GROUP 5 BY title 
   ORDER BY tpw DESC, title ASC OPTION ranker=sph04;

The magic is

1. the GROUP 5 BY that will return up to five items with the same title. If dont want to limit it to 5 just put a really high number!
2. creating the virtual attribute tpw allows you to order the results best first, but keep the items together. the MAX() function gets the highest weighted of all the items with the same title. Because you then order by that, can get the best matches first

(Note you will need to make the name an attribute, so can group by it. Use sql_field_string to make both a field and an attribute :)

... so a pretty advanced feature this GROUP N BY of sphinx, but its also really powerful.