SQLAlchemy query all children's records of a parent

Question

Imagine, I have this simple database model (Parent_id, Child_id are Foreing Keys)

Is there a way how to query straight all Toys objects of certain Parent?

Now I query all Children first and then I query the Toys of each Child and add them to a list or dict. But it's not really an elegant way...

I imagine something simple like (Flask_SQLAlchemy)

Toys.query.filter(Toys.child.parent_id == 'some parent id')

Is it possible?

Thank you

Krystof

I expect to get a SQLAlchemy object containing all Toys of certain parent.

Answer 1

You can join Toy to Child and then Child to Parent (SQLAlchemy can work out how to do the joins, assuming you have configured relationships between all the models):

toys = (Toy.query
           .join(Child)
           .join(Parent)
           .filter(Parent.name == 'parent1')
)