Can any sqlite geniuses help me out here? https://codeberg.org/Safeguarding/sciop/issues/182

How do i do a full text (fts5) query over joined tables? Have searched, find nothing.

for concreteness, we have models over multiple tables - an "upload" is presented on the site as being roughly equivalent to a torrent, but for various reasons like wanting to leave room for other protocols than bittorrent, a lot of the values you would want to search over are in a separate "torrentfile" table. So problem 1: I can't figure out the general sql syntax for doing a FTS query over plain 1:1 joined tables.

We also have part/whole relationships, where e.g. we search over datasets but the thing you might want is in a dataset part. A dataset can have many parts, so problem 2: I can't figure out how to do a joined query over 1:many relationship that weights according to the quality/number of matches in the many child rows.

this feels like something that a sql person would be able to do in their sleep, but i have been scratching my head about it for a few weeks. without this, our search sort of sucks, and we would really like to keep deployment as lightweight as possible and not require running an external search indexing service.