CODEWITHSUNDEEP
sphinxsphinxql
hovado
hovado

Reputation: 4948

Sphinx: Show all results order by previous searches

I use SphinxQL for searching and filtering in product database and I store last x search phrases of each user. I wonder if is it possible to show all products (all rows) to every user but with relevance on previous search.

Let's say one user sought for mobile phones (iphone, galaxy s7...), ie. electronics category. I want to show him all products randomly, but products from category electronics more often and products with those searched keywords even more often.

Is it even possible with Sphinx?

Thanks and sorry for english.

Upvotes: 0

Views: 274

Answers (2)

barryhunter
barryhunter

Reputation: 21091

An alternative, would be perhaps to create random numbers attached to each result. A high and a low number, with an overlapping range. 

sql_query = SELECT id, RAND()*100 AS rand_low, (RAND()*100)+50 AS rand_high, ...
sql_attr_uint = rand_low
sql_attr_uint = rand_high

Can then arrange the ranking expression to pick either of these numbers depending on if matches or not, and sort by the result. 

SELECT id FROM index WHERE MATCH('_all_ MAYBE electronics MAYBE (galaxy s7)') 
OPTION ranker=expr('IF(doc_word_count>1,rand_high,rand_low)');

Will be mixed up. But results that match one of the words, have a greater chance of showing up first (because use the weighted random number) - its still only a chance, because a rand_high CAN still be smaller than rand_low. ... can change the size of the number 'overlap' to tweak the mix of matching/non matching results.

(added as a new answer as its a quite differnt idea, although uses the same 'all' keyword)

Upvotes: 1

barryhunter
barryhunter

Reputation: 21091

Sphinx doesn't have a 'mode' to just do that. But can get very close...

Can use MAYBE operator

MATCH('_all_ MAYBE electronics MAYBE (galaxy s7)')

The complication is need a way to match all products. Depending on your data you may already have a word can use (eg word like 'the' in every single product), or add the word to every document, during indexing.

... using MAYBE allows the matching results to have a higher weight.

But you dont want to sort strictly by weight. So need a different alogithm, something to shuffle the results a bit (as you not really wanting 'random'!) 

SELECT id, IDIV(id/10000) AS int,WEIGHT() AS w 
FROM index WHERE MATCH('_all_ MAYBE electronics MAYBE (galaxy s7)') 
ORDER BY int DESC, w DESC;

This creates banding by ID, as in theory results can be spread over all the id-space will mix them up a bit. But the category results will still tend to be shown first within each band.

If you have one a different attribute other than ID might be better, something more spread out. Or can add a deliberate random attribute to results)

... there are all sort so variations, your imagination is the only limitation, this basic techqiue can be used to mix things up quote a bit.

(There are other possiblities, Sphinxes little known GROUP N BY function, can be used to produce a sampling search result. This is isnt random, but it might give the similar enough result - ie just mixing up results)

Upvotes: 1

Related Questions