2

String with author and his book is given. There are two fields in index: author and title. I need to find all books where author and title matches. String may contain only author or only title, so I can't parse it. If I search

SELECT id FROM books 
WHERE MATCH('@(author, title) "jane smiley horse heaven");

, I don't get the most relevant book with author="jane smiley" and title="horse heaven".

I need something like

SELECT id FROM books
WHERE MATCH('@(title) "horse heaven" @(author) "jane smiley"');

but without splitting the string.

Is it possible?

Prokhor Sednev
  • 668
  • 7
  • 14

1 Answers1

4

MATCH('@(author, title) "jane smiley horse heaven"');

is asking for that exact phrase (quotes are phrase operator). ie those four words sequential in the text. As you say that wont really match.

Instead 

MATCH('@(author,title) (jane smiley horse heaven)');

should mean it just requires those words. But in any field, and technically in any order (so could match against "jane heaven, horse drawn smiley" or whatever ;)

barryhunter
  • 20,886
  • 3
  • 30
  • 43
  • I guess, there should be no space between `MATCH('@(author,title)')`, e.g. column names – Novitoll Jul 28 '17 at 07:30
  • Well spotted. Yes, there shouldn't be a space there. I guess is possible some versions of sphinx are more forgiving, but current version does not like that space. – barryhunter Jul 28 '17 at 10:12