-
-
Notifications
You must be signed in to change notification settings - Fork 121
Description
What is the right way to limit a full text search query to some rows of a table?
For example, I have a table that contains the following columns: title
, content
, owner
(each row represents a document). The owner
column is a username. It feels right to store all documents in one table, instead of having one table per owner. In particular because I'd like to full text search all documents, only documents owned by one user and documents owned by a set of users.
I tried to combine .rows_where("owner = ?", "1234")
and .search()
from the Table
class but I don't think that is meant to work. I discovered .search_sql()
as a way to generate the FTS SQL statement. By hand I can edit it to add a AND [original].[owner] = :owner
to the where
clause. This seems to do what I want.
My two questions:
- is adding a
AND ...
to thewhere
clause actually the right thing to do or should I be doing something else (my SQL skills are low)? - is there a built-in to sqlite-utils way to achieve this?
Right now I am thinking I will make my own version of search_sql()
that generates a query that contains an additional owner = :owner
for my particular use-case.
Bonus question: is this generally useful/something to add to sqlite-utils or too niche?