Add column weights to FTS order by Rank

[izaguirrejoe]

As far as I can tell, you can't currently run a Full-text search and order by rank in SQLite.swift.

SELECT * FROM fts WHERE fts MATCH ? ORDER BY bm25(fts);
SELECT * FROM fts WHERE fts MATCH ? ORDER BY rank;

Is this feature currently available? If not, how do we order the results of a Full-text search by relevance? Is there currently a workaround?

[dbannach]

I was looking for the same feature. It seems you can just append .order("rank") to your query:

let fts = VirtualTable("fts")
...

let query = fts
    .filter(fts.match("some search string"))
    .order("rank")

for fts in db.prepare(query) {
    ...
}

But how can we define different weights for columns? Like here:

SELECT * FROM fts WHERE fts MATCH 'some search string' AND rank MATCH 'bm25(10.0, 1.0)' ORDER BY rank

[izaguirrejoe]

It seems to work. Yeah, the next step is defining weights for the columns.

[dbannach]

When I define 'rank' as an expression of String type it lets me use the match() function on it. Not very Swift-y but it seems to work:

let rankFunction = Expression<String>("rank")

let query = fts
    .filter(fts.match("some search string"))
    .filter(rankFunction.match("bm25(10.0, 1.0)"))
    .order("rank")

[izaguirrejoe]

Actually, neither seem to do anything. Strange.

[izaguirrejoe]

I replaced "rank" with some nonsense string and the result is the same.

[ryanpato]

I was looking for the same feature. It seems you can just append .order("rank") to your query:

let fts = VirtualTable("fts")
...

let query = fts
    .filter(fts.match("some search string"))
    .order("rank")

for fts in db.prepare(query) {
    ...
}

But how can we define different weights for columns? Like here:

SELECT * FROM fts WHERE fts MATCH 'some search string' AND rank MATCH 'bm25(10.0, 1.0)' ORDER BY rank

I think you can't give a hardcoded "rank" string, but an expression: let rank = Expression<String>("rank"), it seems the data type doesn't matter too much, String or Int works.

let entriesFTS = VirtualTable("entries_fts")
let rank = Expression<String>("rank")

let replies = entriesFTS
    .filter(entriesFTS.match("text:\(string)"))
    .select(text)
    .order(rank)

At the least, it matches up with what I get querying the DB directly 🤷‍♂️

SELECT * 
FROM entries_fts
WHERE glosses MATCH 'text' 
ORDER BY rank;