On 08/10/2016 12:47 AM, Jan Berkel wrote:
I'm currently implementing FTS5 in my application and I'm at the stage
where
I want to rank the results in an efficient way. I'm following the
examples
from "Appendix A: Search Application Tips"
(https://www.sqlite.org/fts3.html#appendix_a).
Similar to the example there I have a static weight component (a
separate rank
table with scores) which I want to combine with a query-specific ranking
function. The FTS4 example from the appendix reads:
SELECT docid, rank(matchinfo(documents), documents_data.weight) AS
rank
...
ORDER BY RANK desc
The rank function gets the static score passed in and calculates a new
score,
taking the query into account.
In FTS5, rank functions are defined with
SELECT ... WHERE fts MATCH ? AND rank MATCH 'functionName(parameters)'
ORDER BY RANK
The parameters need to be SQL literals, therefore I can't
pass in arbitrary data as done in the previous FTS4 example.
What would be the best way to pass the data to the function, in order to
achieve similar results?
As far as I can tell the function does not have access to the "outer"
data.
If an auxiliary fts5 function is bound to the "rank" column, it cannot
be passed extra parameters from the outer query. The reason is that when
you use "ORDER BY rank", the virtual table module sorts the rows before
returning them to the SQLite core. And of course, the virtual table
module knows nothing about the outer query - it only knows that SQLite
requested a set of matching rows in sorted order.
You could run a regular database query from within the auxiliary
function implementation. Not sure how that would perform though -
perhaps similarly to the FTS3/4 approach, perhaps a little better.
Or, you could add the "weight" as an UNINDEXED column to the fts5
documents table. Then access it via the auxiliary function xColumn() API.
Dan.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
