SQLAlchemy SQL expression with JSONB query

I have the following property on a flask-sqlalchemy model. I want to make this approved_at property a sortable column in flask-admin, but apparently I need to convert this to a hybrid property using SQL expressions.

@property
def approved_at(self):
    approved_log = (
        db.session.query(AuditLog)
        .filter_by(target_id=self.id, target_table='consult')
        .filter(AuditLog.new_values['status'].astext == "APPROVED: 5")
        .order_by(AuditLog.timestamp.desc())
        .first()
    )
    if approved_log:
        return approved_log.timestamp

I don’t know how to convert this query into a sqlalchemy SQL expression, since it’s pretty complex with the JSONB query in it. I’ve looked at all the other SO answers, haven’t been able to figure it out.

Can I get some help on how to do this? Or alternatively, how to make a sortable column in Flask Admin that doesn’t require me to use hybrid expressions?

Answer

Implementing it as a hybrid is somewhat straightforward:

@hybrid_property
def _approved_at(self):
    return (
        db.session.query(AuditLog.timestamp)
        .filter_by(target_id=self.id, target_table='consult')
        .filter(AuditLog.new_values['status'].astext == "APPROVED: 5")
        .order_by(AuditLog.timestamp.desc())
        .limit(1)
    )

@hybrid_property
def approved_at(self):
    # return the first column of the first row, or None
    return self._approved_at.scalar()

@approved_at.expression
def approved_at(cls):
    # return a correlated scalar subquery expression
    return cls._approved_at.as_scalar()