I’m trying to execute an FTS5 query using the C API, and need to restrict
the query to a specific column. In FTS4, this was possible by doing:
SELECT foo, bar FROM tableName WHERE columnName MATCH ?
and then binding the search string to the statement. However, with FTS5,
the LHS of the MATCH operator must be the FTS table name itself, and the
column name must be a part of the query:
SELECT foo, bar FROM tableName WHERE tableName MATCH 'columnName:' || ?.
This works when the bound string is a single phrase. However, consider the
SELECT foo, bar FROM tableName WHERE tableName MATCH 'columnName:pizza is
Only pizza is restricted to to the columnName, but the rest of the phrase
is matched against all columns. In my use case, I don’t know the contents
of the search text, and parsing it to find each phrase and then append a
column filter to it is a rather daunting proposition, given the myriad ways
a FTS 5 query can be structured. How can I work around this? If the
filtering isn’t possible at the query level, is there a way I can inspect
the results received from sqlite3_step and check which column it was
matched against? I already had to reimplement the offsets() function for
FTS5, so could the same auxiliary functions API be leveraged to give me the
information I require?