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?
Advertisement
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()