Filter rows in child table using parent table having one to many relationship sqlalchemy

Question

I have two tables having one to many relationship.

I want to find all rows of child where type is "abc"

class Parent(base):
    __tablename__ = "parent"
    id = Column("id", String, primary_key=True)
    parent = relationship("Child", back_populates="child")


class Child(base):
    __tablename__ = "child"
    id = Column("id", Integer, primary_key=True)
    name = Column(String)
    tid = Column(String, ForeignKey("parent.id"))
    child = relationship(Tenant, back_populates="parent")
    type = Column(String)


return self.session.query(Parent.parent).filter_by(Parent.parent.type == "abc").all()

It gives me error as InstrumentedAttribute' object nor 'Comparator' object associated with Parent.parent has an attribute 'type'

And if i do

return self.session.query(Parent).filter(Parent.parent.any(Child.type == type)).all()

It gives me all rows with other types as well

snakecharmerb · Accepted Answer · 2021-01-19T16:33:04.127

This would do what you want:

from sqlalchemy.orm import contains_eager

q = (session.query(Parent)
            .join(Child)
            .options(contains_eager(Parent.parent))
            .filter(Child.type == 'abc'))

for p in q:
    print(p.id, [c.name for c in p.parent])

The contains_eager function tells sqlalchemy that only the rows of the referenced collection specified in the query should be eagerly loaded: in the above example, only Child instances with a type of 'abc'.

The linked docs warn that by returning only a selection of a related collection can lead to problems: it's worth reading the warning and bearing it in mind when designing your application.

Filter rows in child table using parent table having one to many relationship sqlalchemy

1 Answers1