issue_comments: 789409126

This data as json

html_url issue_url id node_id user created_at updated_at author_association body reactions issue performed_via_github_app
https://github.com/simonw/datasette/issues/268#issuecomment-789409126 https://api.github.com/repos/simonw/datasette/issues/268 789409126 MDEyOklzc3VlQ29tbWVudDc4OTQwOTEyNg== 649467 2021-03-03T03:57:15Z 2021-03-03T03:58:40Z NONE

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.

 
{
    "total_count": 0,
    "+1": 0,
    "-1": 0,
    "laugh": 0,
    "hooray": 0,
    "confused": 0,
    "heart": 0,
    "rocket": 0,
    "eyes": 0
}
 323718842