Search code examples
sqlalchemyfull-text-search

SQLAlchemy SQLite FTS MATCH on entire index, not just a single column


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.


Solution

  • 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.