Mechanism for ranking results from SQLite full-text search

[simonw]

This isn't particularly straight-forward - all the more reason for Datasette to implement it for you. This article is helpful: http://charlesleifer.com/blog/using-sqlite-full-text-search-with-python/

[simonw]

I did a bunch of research relevant to this a while ago: https://simonwillison.net/2019/Jan/7/exploring-search-relevance-algorithms-sqlite/

[simonw]

I want this on the table page - but that means that the table page will need to run a slightly more complex query since it needs access to a rank column to sort by - which it gets from running a join.

BUT... that join needs to be constructed in a way that keeps existing filters, ?_where= clauses etc intact.

Here's a prototype using SQLite CTEs: https://register-of-members-interests.datasettes.com/regmem?sql=with+original+as+%28select+rowid%2C+*+from+items%29%0D%0Aselect%0D%0A++original.*%2C%0D%0A++items_fts.rank+as+items_fts_rank%0D%0Afrom%0D%0A++original+join+items_fts+on+original.rowid+%3D+items_fts.rowid%0D%0Awhere%0D%0A++items_fts+match+escape_fts%28%3Asearch%29%0D%0Aorder+by+items_fts_rank+desc+limit+10&search=hotel

with original as (
  select
    rowid,
    *
  from
    items
)
select
  original.*,
  items_fts.rank as items_fts_rank
from
  original
  join items_fts on original.rowid = items_fts.rowid
where
  items_fts match escape_fts(:search)
order by
  items_fts_rank desc
limit
  10

[simonw]

Worth noting that joining to get the rank works for FTS5 but not for FTS4 - see comment here: simonw/sqlite-utils#192 (comment)

Easiest solution would be to only support sort-by-rank for FTS5 tables. Alternative would be to depend on https://github.com/simonw/sqlite-fts4

[simonw]

I should depend on sqlite-fts4 - I'm doing that in sqlite-utils now and it works great: simonw/sqlite-utils#198

[simonw]

Part of the challenge here is that this is the first time the TableView will have had a complete rewrite of the SQL it is going to execute. That SQL is currently constructed here:

datasette/datasette/views/table.py

Lines 628 to 636 in 5eb8e9b

	sql_no_limit = "select {select} from {table_name} {where}{order_by}".format(
	select=select,
	table_name=escape_sqlite(table),
	where=where_clause,
	order_by=order_by,
	)
	sql = "{sql_no_limit} limit {limit}{offset}".format(
	sql_no_limit=sql_no_limit.rstrip(), limit=page_size + 1, offset=offset
	)

[mhalle]

In FTS5, I think doing an FTS search is actually much easier than doing a join against the main table like datasette does now. In fact, FTS5 external content tables provide a transparent interface back to the original table or view.

Here's what I'm currently doing:

• build a view that joins whatever tables I want and rename the columns to non-joiny names (e.g, chapter.name AS chapter_name in the view where needed)
• Create an FTS5 table with content="viewname"
• As described in the "external content tables" section (https://www.sqlite.org/fts5.html#external_content_tables), sql queries can be made directly to the FTS table, which behind the covers makes select calls to the content table when the content of the original columns are needed.
• In addition, you get "rank" and "bm25()" available to you when you select on the _fts table.

Unfortunately, datasette doesn't currently seem happy being coerced into doing a real query on an fts5 table. This works:
select col1, col2, col3 from table_fts where coll1="value" and table_fts match escape_fts("search term") order by rank

But this doesn't work in the datasette SQL query interface:
select col1, col2, col3 from table_fts where coll1="value" and table_fts match escape_fts(:search) order by rank (the "search" input text field doesn't show up)

For what datasette is doing right now, I think you could just use contentless fts5 tables (content=""), since all you care about is the rowid since all you're doing a subselect to get the rowid anyway. In fts5, that's just a contentless table.

I guess if you want to follow this suggestion, you'd need a somewhat different code path for fts5.

[mhalle]

It's kind of an ugly hack, but you can try out what using the fts5 table as an actual datasette-accessible table looks like without changing any datasette code by creating yet another view on top of the fts5 table:

create view proxyview as select *, rank, table_fts as fts from table_fts;

That's now visible from datasette, just like any other view, but you can use fts match escape_fts(search_string) order by rank.

This is only good as a proof of concept because you're inefficiently going from view -> fts5 external content table -> view -> data table. However, it does show it works.