SQLAlchemy: Is there a way to append raw query to query object?

Question

I'm trying to create a custom query with BigQuery in Google

I can do almost everything using SQLAlchemy filter function

query = TestModel.query.filter(TestModel.timestamp.__gt__(1010101010))

But I want to add custom filter on where clause like below

AND EXISTS (SELECT * FROM UNNEST(column_name) WHERE column_key LIKE '%tit%')

Is there a way to append filter using string like above?

Answer 1

Raw query

You can clearly do this like below:

text_clause = text("EXISTS (SELECT * FROM UNNEST(column_name) WHERE column_key LIKE '%tit%')")
ts = 1010101010
q = (
    session.query(TestModel)
    .filter(TestModel.timestamp > ts)
    .filter(text_clause)
)

But it is going to be cleaner and more maintainable to avoid it if you can.

ORM Query Below should give you an idea on how to implement it:

ts = 1010101010

subq = (
    select(literal("*"))
    .select_from(func.unnest(TestModel.column_name))
    .filter(TestModel.column_key.contains("tit"))
).exists()

q = (
    session.query(TestModel)
    .filter(TestModel.timestamp > ts)
    .filter(subq)
)