CODEWITHSUNDEEP
pythonsqlalchemy
k107
k107

Reputation: 16440

How to use a subquery to filter a sqlalchemy query on a one to many relationship?

So if I have two sqlalchemy tables like

class Parent
    id = Column(Integer, primary_key=True)
    children = relationship('Child', lazy='joined', backref=backref('parent', lazy='joined'))
class Child
    id = Column(Integer, primary_key=True)
    age = Column(Integer)

How do I find all the parents that have at least one child with age > 10?

I've tried something like this, though this doesn't work:

Session.query(Parent.id).filter(func.count(Parent.children.filter(Child.age >= 10)) > 0)

Upvotes: 0

Views: 1570

Answers (1)

van
van

Reputation: 76962

Assuming this model:

class Parent(Base):
    id = Column(Integer, primary_key=True)
    name = Column(String)
    children = relationship('Child', lazy='joined',
                            backref=backref('parent', lazy='joined'))

class Child(Base):
    id = Column(Integer, primary_key=True)
    parent_id = Column(ForeignKey(Parent.id))
    age = Column(Integer)

use any construct to get the filter working:

q = (session.query(Parent)
     .filter(Parent.children.any(Child.age >= 10))
     )

for p in q:
    print("{}".format(p))
    for c in p.children:
        print("  {}".format(c))

Upvotes: 2

Related Questions