Filtering All Phrases In a FTS 5 Match Query

Previous Topic Next Topic
classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
Report Content as Inappropriate

Filtering All Phrases In a FTS 5 Match Query

Aditya Krishnadevan
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?

Aditya Krishnadevan
sqlite-users mailing list
[hidden email]