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?
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 ;)