sphinxsphinxql

SphinxQL Variables Deprecated, Alternate Query?

I had what I thought was a fairly straightforward SphinxQL query, but it turns out @ variables are deprecated (see example below)

SELECT *,@weight AS m FROM test1 WHERE MATCH('tennis') ORDER BY m DESC LIMIT 0,1000 OPTION ranker=bm25, max_matches=3000, field_weights=(title=10, content=5);

I feel like there must be a way to sort the results by strength of match. What is the replacement?

On another note, what if I want to include in it a devaluation if certain other words appear. For example, let's say I wanted to devalue results that had the word "apparel" in them. Could that be executed in the same query?

Thanks!

Solution

  • Well results are 'by default' in weight decending, so just do... 

    SELECT * FROM test1 WHERE MATCH('tennis') LIMIT 0,1000 OPTION ...

    But otherwise its, just the @ variables, are replaced by 'functions' mainly because its more 'SQL like'. So @weight, is WEIGHT()

    SELECT * FROM test1 WHERE MATCH('tennis') ORDER BY WEIGHT() DESC ...

    or 

    SELECT *,WEIGHT() AS m FROM test1 WHERE MATCH('tennis') ORDER BY m DESC ...

    For reference @group is instead GROUPBY(), @count is COUNT(*), @distinct is COUNT(DISTINCT ...), @geodist is GEODIST(...) , and @expr doesnt really have an equivlent, either just use the expression directly, or use your own custom named alias.

    As for second question. Kinda tricky, they isnt really a 'negative' weighter. Ther is a keyword boost operator, but as far can't use it to specifically devalue.

    The only way I can think maybe have it work, is if negative match was against a specific field, could build a complex ranking exspression. Basically as a negative weight instead, would need a specific field for the ranking expression, so could use to select that column 

    ... MATCH('@!(negative) tennis @negative apparel') 
... OPTION ranker=expr('SUM(word_count*IF(user_weight=99,-1,1))'), field_weights(negative=99)

    That's a very basic demo expression for illustrative purposes, a real one would probably be a lot more complex. Its just showing using 99 as a placeholder for 'negative' multiplication. Would need the new negative field creating, which could just be a duplicate of other field(s)