Closed betatim closed 2 years ago
Yeah, at the moment the best way to do this is with search_sql()
, but you're right it really isn't very intuitive.
Here's how I would do this, using a CTE trick to combine the queries:
search_sql = db["articles"].search_sql(columns=["title", "author"]))
sql = f"""
with search_results as ({search_sql})
select * from search_results where owner = :owner
"""
results = db.query(sql, {"query": "my search query", "owner": "my owner"})
I'm not sure if sqlite-utils
should ever evolve to provide a better way of doing this kind of thing to be honest - if it did, it would turn into more of an ORM. Something like PeeWee may be a better option here.
Actually I have a thought for something that could help here: I could add a mechanism for inserting additional where filters and parameters into that .search()
method.
That would be handy (additional where filters) but I think the trick with the with
statement is already an order of magnitude better than what I had thought of, so my problem is solved by it (plus I got to learn about with
today!)
I added where=
and where_args=
parameters to that .search()
method - updated documentation is here: https://sqlite-utils.datasette.io/en/latest/python-api.html#searching-with-table-search
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). Theowner
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 theTable
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 aAND [original].[owner] = :owner
to thewhere
clause. This seems to do what I want.My two questions:
AND ...
to thewhere
clause actually the right thing to do or should I be doing something else (my SQL skills are low)?Right now I am thinking I will make my own version of
search_sql()
that generates a query that contains an additionalowner = :owner
for my particular use-case.Bonus question: is this generally useful/something to add to sqlite-utils or too niche?