issues: 1257724585

This data as json

id node_id number title user state locked assignee milestone comments created_at updated_at closed_at author_association pull_request body repo type active_lock_reason performed_via_github_app reactions draft state_reason
1257724585 I_kwDOCGYnMM5K91qp 441 Combining `rows_where()` and `search()` to limit which rows are searched 1448859 closed 0     4 2022-06-02T06:01:55Z 2022-06-14T21:57:57Z 2022-06-14T21:54:38Z NONE  

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: 1. is adding a AND ... to the where clause actually the right thing to do or should I be doing something else (my SQL skills are low)? 2. 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?

 140912432 issue     
{
    "url": "https://api.github.com/repos/simonw/sqlite-utils/issues/441/reactions",
    "total_count": 0,
    "+1": 0,
    "-1": 0,
    "laugh": 0,
    "hooray": 0,
    "confused": 0,
    "heart": 0,
    "rocket": 0,
    "eyes": 0
}
   completed

Links from other tables