SQLite Forum

FTS5: calculating column weight dynamically?
Missed this when it was first posted a few days ago.

I don't see any reason that is not possible, if I understand correctly. You might need to write new FTS5 custom function. The built-in bm25() function is just an example of an FTS5 custom function, which is really just an SQLite scalar user function that has access to this API:


That API can read any columns of the row the custom function is operating on (calculating a match score for), so if it can determine the nesting level based on that, it could return an adjusted match score as desired. Or, if the nesting level information is found elsewhere in the database you could pass it in to each invocation of the custom function via one of its trailing arguments.

One limitation is that if you want to install the custom function so that fts5 queries can use (ORDER BY rank) instead of "ORDER BY customfunction()", which is more efficient, all trailing arguments must be constants. So the nesting level would have to be read from the current row in that case. 

Another is that it is more efficient to avoid accessing table data from FTS5 custom functions (as this requires a table lookup). Encoding the nesting level using some bits from the 64-bit fts5 table rowid value is one way around this.