1

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?

1 Answer 1

0

Try limit(1) instead of first. One is for the ORM, the other for core (which you are using)

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.