0

Is it possible to do any sort of Union using SphinxQL? I want to return one set of results containing two queries and in order of the query. A simple example would be:

Select Author from idx_jobs where MATCH('@(Author) Steinbeck') Union Select Author from idx_jobs where MATCH('@(Description) Steinbeck')

Naturally I could do

Select Author from idx_jobs where MATCH('@(Author, Description) Steinbeck')

but I'm trying to provide some control over 'relevance' in the results.

user3649739
  • 1,829
  • 2
  • 18
  • 28
  • @BarryHunter I did some online searching and found a thread of yours from here http://sphinxsearch.com/forum/view.html?id=9505. I'm unclear reading the doc on your UDF if it would address this. I am not doing a Group per se; I am doing two searches and trying to get unique records from them. It would be relevant as well say I did one version as `(@(BookTitle) Potter @(Description) Harry` and one as `(@BookTitle) Harry Potter` so I could get all books with `Harry` and `Potter` in them but prioritize the ones where it is just in the title. – user3649739 Mar 26 '17 at 19:19
  • that thread had much more complicated requirements to you. a UDF would be OTT. – barryhunter Mar 27 '17 at 10:42
  • @Barryhunter OTT? I'm not sure if his needs were in fact more complicated... – user3649739 Mar 28 '17 at 12:40
  • @Barryhunter The more I look at the OP's question on your UDF and my needs the more compatible they seem; I need to basically have two 'groups' each of which ranks by a field_weight I assign; the first group are all the Featured Ads (1) and the second are the non-Featured (0) ads. So I need: 1) Featured High field_weight 2) Featured Low Field Weight 3) Non-Featured High Field Weight 4) Non-featured Low Field Weight. I cannot combine ORDER and field_weight in any way that works even though I've gotten field_weight to work. Any idea on how I could use your UDF to achieve this? – user3649739 Mar 29 '17 at 01:23

1 Answers1

0

No union.

But seems like field weight would be useful http://sphinxsearch.com/docs/current.html#sphinxql-select

.... OPTION field_weights=(author=1000)

Should put the matches against the author field first. (ie add that to end of second query)

barryhunter
  • 20,886
  • 3
  • 30
  • 43
  • The problem is is that it isn't weight on one field per se but on one construction. Say this were products and it was in the `Item` and `Description` fields and the user searched on `White Ipad`. Search is say `@(Item) White Ipad|(@(item) Ipad @(Description) White`. I'll get results both with 'White Ipad` and with 'Used Ipad` and description has `bla bla white` and while i may in this case want both (a soft search with a hard search) I'd like to prioritize those that match in the title so `White Ipad` and `I have an Ipad, White for sale` in the title. – user3649739 Mar 28 '17 at 12:45
  • Got to say think field weights will do that. – barryhunter Mar 28 '17 at 14:19
  • No such luck I get the exact same results with the option field as I do w/o – user3649739 Mar 28 '17 at 19:37
  • `Select Item,Description from Books where MATCH(@(Item) Ipad @(Item,Description) White`) OPTION field_weights=(item=1000)` is the same as ``Select Item,Description from Books where MATCH(@(Item) Ipad @(Item,Description) White`)` both if which mix results with `White` in the title. – user3649739 Mar 28 '17 at 20:11
  • In fact I ran with `Select Weight()` to confirm it is sorting by weight which it IS yet for some reason `Ipad` is weighted higher than `Ipad available in White`. – user3649739 Mar 28 '17 at 20:17
  • The issue appears to be that as constructed 'Ipad' alone has the highest weight. I am basically trying to say 'Find me all records where Ipad is in the title and White is in the title or the description but weight White being in the title as well highest`. Again this would be akin to doing 'Select * from Items where Title like '%Ipad% and Title like %White% Union Select * from Items where Title like %Ipad% and Description like '%White`. Wondering if two different indexes would work (which I believe you once said is like a Union in Sphinx. – user3649739 Mar 28 '17 at 20:28