how do i perform a self-referential correlated subquery in sqlalchemy?

If I were to write this in SQLAlchemy, I’d use sqlalchemy.exists() and sqlalchemy.orm.aliased. The latter allows you to have essentially a named copy of the model, so you can distinguish between the same table:

from sqlalchemy import exists
from sqlalchemy.orm import aliased

other = aliased(TransactionModel)

TransactionModel.query.filter(
    TransactionModel.chain == "somevalue",
    ~exists().where(
        (other.overwrite_id == TransactionModel.id) &
        (other.chain == TransactionModel.chain)
    )
)

or equivalently (using and_ instead of &)

from sqlalchemy import and_, exists
from sqlalchemy.orm import aliased

other = aliased(TransactionModel)

TransactionModel.query.filter(
    TransactionModel.chain == "somevalue",
    ~exists().where(and_(
        other.overwrite_id == TransactionModel.id,
        other.chain == TransactionModel.chain
    ))
)

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top