SQLAlchemy: filter single relationship attribute by multiple values

Question

Say I have these 2 objects, with a one-to-many relationship:

class A(Base):
    ...
    collection = relationship("B")

class B(Base):
    ...
    a_id = Column(Integer, ForeignKey('table_for_a.id'), nullable=False)
    key = Column(String(50), nullable=False)

How can I query A's which have B("apple"), B("orange") and B("banana") in A.collection at the same time?

Thanks.

Answer 1

After a while digging, I found out that I could get the desired result like so:

from sqlalchemy import and_

...
session.query(A).filter(
    and_(
        A.collection.any(key="apple"),
        A.collection.any(key="orange"),
        A.collection.any(key="banana")
    )
).all()