Query for a specific child of a parent in SQLAlchemy

Question

I'm trying to write a query that will return exactly one child object from a parent.

My model is as follows:

class Parent (Base):
    __tablename__ = 'parents'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    children_id = Column(Integer, ForeignKey('children.id'))
    childern = relationship('Child', back_populates='parents')

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

Consider this example: there are three parents with their children:

Alice: Zander, Ygrite, Xaiver
Bob: Will, Ulric, Tammy
Chris: Will, Sam, Xaiver

Notice that Chris has coincidentally named some of his children the same as the others, even though they are distinct children.

If I want to find specifically Chris's child Xavier my first instinct is to either query for Parent Chris then iterate over his children until I find Xavier or to query for all children named Xavier then to iterate over them until I find the one with parent Chris.

However, this solution does not scale and definitely doesn't use SQLAlchemy's capabilities.

Answer 1

In continuing to research this challenge I came across this question and the accepted response: SQLAlchery query filter on child attribute

I tested this query...

s.query(Child).join(Parent, Child.parent).filter(Child.name == 'Xavier').filter(Parent.name == 'Chris')

...and it does exactly what I want it to do.