trying (and failing) to wrap my head around how to use SQLAlchemy in the following scenario:
Assume I have a Database such that:
A has a (one -> many) relationship to B
B has a (one -> many) relation to C
If I want to loop through all the B's given an A, I can do:
for b in a.bs:
print "hello"
If I want to loop through all C's that indirectly belong to A, I can do:
for b in a.bs:
for c in b.cs:
print "hello"
However, I am aware that each iteration of the outer-most loop would perform a fresh SQL query.
I am of the understanding that I can use subqueryload to prevent this from happening by doing:
for b in session.query( b ).options( subqueryload( B.c ) ).filter_by( B.a_id == a.id ):
for c in b.cs:
print "hello"
Is that the tidiest way to do it?
Is there not some syntax that lets me start from the actual 'a' object. Perhaps something like:
for b in a.bs.options( subqueryload( B.c ) ):
...
Many thanks in advance for your help
You can subqueryload an arbitrary join path like this:
session.query(A).options(subqueryload(A.b).subqueryload(B.c))
This uses three queries and will load A.b, and B.c for every B in A.b.