Relation in sqlalchemy

Question

I Use sqlalchemy ORM and pydantic
I have the following simple table

class Parent(Base):
    __tablename__ = 'parents'
    id = Column(Integer, primary_key=True, nullable=False)
    children = relationship(
        "Child",
        backref="parent",
        uselist=True
    )

class Child(Base):
    __tablename__ = 'children'
    id = Column(Integer, primary_key=True, nullable=False)
    parent.id = Column(Integer, ForeignKey(Parent.id))
    age = Column(Integer)

Getting the parents and their children is easy
But how can i filter that child with age?
If you get the following and then serialize it with pydantic, all children will be nested

parents = db.query(Parent).all()

Answer 1

You need to do a join between your parent table and your child table to do a filter on the child.

parents = db.query(Parent).join(Child, Child.parent.id == Parent.id).filter(Child.age < 18).all()