SQLite FTS 5 allows matching on the entire index:
SELECT product_name, description, retail_price
FROM auctionlot_idx
WHERE auctionlot_idx MATCH 'mx AND master AND dongel'
This will match documents that have mx & master and dongel in the FTS index. I am trying to use integrate this functionality with SQLAlchemy, following this excellent answer as a guide. Near the end of the answer, it demonstrates how to use the MATCH operator:
stmt = select(AuctionLotIdx).where(AuctionLotIdx.product_name.op("MATCH")("mx master"))
I was wondering what the best way to generate a query like the one above while staying as much as possible in the SQLAlchemy way of doing things.
The following is what I thought could be a workaround, but is not equivalent, for example one of the terms may appear only in one column:
SELECT product_name, description, retail_price
FROM auctionlot_idx
WHERE auctionlot_idx.product_name MATCH 'mx AND master AND dongel'
OR auctionlot_idx.description MATCH 'mx AND master AND dongel'
OR auctionlot_idx.title MATCH 'mx AND master AND dongel'
Since none of the columns match the entire condition on their own, this query won't work.
As noted by @python_user and in a comment of the very answer I linked, the way to do this is with column
and the way I used it as follows:
def fts_column_MATCH(table: Table) -> Callable[[Any], BinaryExpression[Any]]:
return column(table.__tablename__).op("MATCH")
# later
stmt = select(AuctionLotIdx).where(fts_column_MATCH(AuctionLotIdx)("some FTS query"))
Maybe there's a more SQLAlchem-ic way to write this to be more consistent with the style, but this works for me.