0

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.

barryhunter
  • 20,886
  • 3
  • 30
  • 43
hovado
  • 4,474
  • 1
  • 24
  • 30

2 Answers2

1

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)

barryhunter
  • 20,886
  • 3
  • 30
  • 43
  • Thanks Barry. Based on your suggestion I create virtual column in index `SELECT ..., 'sphinxsearch' as sphinxsearch ...`. Now I can match all products like `MATCH ('sphinxsearch MAYBE (galaxy s7)')`. But it's still quite strict and I have to somehow shuffle those results. I take a look at GROUP N BY. – hovado Apr 28 '16 at 20:38
  • Note GROUP N BY isnt really a shuffling system, its in effect a sampling system. Can get say maximum 10 results from each category (assuming have category in an attribute) - which tends to mix things up a bit, but still have to be careful sorting results. – barryhunter Apr 29 '16 at 18:10
  • This thread: http://sphinxsearch.com/forum/view.html?id=9657 might be a interesting solution. I made a UDF to mix results around. – barryhunter Apr 29 '16 at 18:13
1

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)

barryhunter
  • 20,886
  • 3
  • 30
  • 43
  • Simple and clever - I like it. And after each reindexing user gets new set of results. Thanks Barry, you are sphinx king :) – hovado May 02 '16 at 17:30
  • As a user, tend to prefer these largely 'stable' random ordering. Its frustrating when get a page of nice results, click one, and then go back to look at another, the selection is completely different. But long term the results will be 'evolving'. – barryhunter May 02 '16 at 18:41