Let's say I have the following models. A pizza's status can be updated multiple times, but I want my Pizza model to have a latest_status which returns the most recent status:
class PizzaStatus(Base):
updated_at = Column(DateTime, nullable=False, server_default=func.now())
type = Column(Integer, nullable=False)
pizza_id = Column(Integer, ForeignKey("pizzas.id"))
class Pizza(Base):
id = Column(Integer, primary_key=True)
# Always load the statuses when querying an object
statuses = relationship("PizzaStatus", backref="pizza", lazy="selectin")
@typed_hybrid_property
def latest_status(self):
return max(self.statuses, key= lambda status: status.updated_at)
@latest_status.expression
def latest_status(cls):
return select([Pizza.statuses]).where(PizzaStatus.pizza_id == cls.id).order_by(desc(PizzaStatus.updated_at)).first()
I can, when I have fetched a Pizza object, do this:
pizza = session.query(Pizza).all()
pizza.latest_status
Which means the hybrid property implemented on the instance level of Pizza works.
However, I can't do this:
session.query(Pizza).filter(Pizza.latest_status.type.in_(1, 2, 3, 4))
I get this error:
AttributeError: 'Select' object has no attribute 'first'
Here is what I would like to achieve: when querying Pizza objects from my database, I want to filter pizzas based on their latest status. I want these statuses to be in a list of valid status types. I have tried approaches like this one: sqlalchemy hybrid_attribute expression, but I couldn't get them to work. Could you give me a hand please?