SQLAlchemy: Filter the Objects From Relationship When Accessed

• Query Statement: Get Children that have their Names start with ‘A’. Link Them With their Parents. Schema:

• I Have a One-to-Many Relationship. Parent(id, Name, children(rel->child)) -> Child(id, Name)

class Parent(Base):
    __tablename__ = 'parent'

    id = Column(Integer, primary_key=True)
    Name = Column(String)
    children = relationship("Child", lazy='joined')

class Child(Base):
    __tablename__ = 'child'

    id = Column(Integer, primary_key=True)
    Name = Column(String)

• Want to Filter the Relationship Object. (ie, the List of Objects you get when an instantiated Parent.children) is accessed.

• Eg: [Parent(id=1, Name='Parent1' children=[Child(id='1', Name='AChild1'), Child(id='2', Name='Child2')] Needs to be Filtered to: [Parent(id=1, Name='Parent1' children=[Child(id='1', Name='AChild1')] when the resulting query is accessed.

How do I write a Statement to get the Above Result?

A solution that Filters them Once Loaded, I want to Filter them While Loading.

Answer

Perhaps I should’ve googl-fu’ed Harder, but this was the result of some searching.

From SQLAlchemy Documentation:

When we use contains_eager(), we are constructing ourselves the SQL that will be used to populate collections. From this, it naturally follows that we can opt to modify what values the collection is intended to store, by writing our SQL to load a subset of elements for collections or scalar attributes.

Resulting in the Statement:

db.Query(Parent).join(Parent.children)
                .filter(Parent.children.any(Child.Name.like("A%")))
                .options(contains_eager(Parent.children))