lundi 6 juillet 2015

Combine MATCH with OR clause in the WHERE statement

I want to perform a query in which the WHERE clausule has the following condition:

  • one MATCH condition over a column in a FTS3 table OR
  • another not MATCH condition over a column in a non FTS table.

Example:

Say that I have two tables

  • books_fts, which is a table with a content column for full text search.
  • books_tags, which is non FTS table with tags.

I want to search all the books that either contain 'Dikjstra' in their content or are tagged with the 'algorithm' word. So I run this query:

SELECT * from books_fts               
LEFT OUTER JOIN books_tags ON books_fts.fk_id = books_tags.id
WHERE (books_fts MATCH 'content:%Dijkstra*')
OR (books_tags.tag = 'algorithm')

I think the query is right, and if I run it with either one of the OR clausules, it works. However, when running it with the two clausules I get the following error:

unable to use function MATCH in the requested context

Seems to me that I cannot combine a MATCH with a non MATCH in the WHERE clause, even if each of them apply to different tables (one FTS and another non FTS). Is this true? I cannot find information on it.

NOTE: if the causules are separated with AND instead of OR the query is valid.

Thanks.

Aucun commentaire:

Enregistrer un commentaire