On Sep 15, 5:38 am, Crusty <[email protected]> wrote:
> Hey everyone,
>
> sorry for the title, I couldnt think of any way to describe this in
> short.
> I have 3 Classes, which have basically this relationship:
>
> 1 Class1 has n Class2 ( 1:n)
> 1 Class2 has n Class3 ( 1:n)
>
> So basically it looks like this:
>
> Class1
> |-- Class2
> |-- Class3
>
> Now if I join them all together, i get something like this:
>
> Class1 Class2 Class3
> --------------------------------------
> 1 1 1
> 1 1 2
> 1 2 1
> 1 2 2
> 2 1 1
> 2 1 2
> 2 2 1
> 2 2 2
> .... etc
>
> so if I loop through the results i would have something like this:
>
> for (class1, class2, class3) in results:
> print class1, class2, class3
>
> But what I would really like to do is:
>
> for (class1, class2_results) in class1:
> print "results for class1:
> for (result, class3_results) in class2_results:
> print "results for class2:"
> for result in class3_results:
> print "result"
>
> which will give me an output more like this:
>
> results for class1:
> result1
> results for class2:
> result1
> ....
>
> And so on.
> In short, I want to get get xxx rows of class1 repeating, but I want
> to get one result per class1, containing nested results.
>
> Is that possible and do I need subqueries for that?
>
> Greetings,
>
> Tom
As long as you have ORM relations set up (I will assume you have
Class1.class2_results and Class2.class3_results), you can use
eagerloading to get your nested loops while still sending only one
query to the database:
q = session.query(Class1)
q = q.options(eagerload_all("class2_results.class3_results"))
for class1 in q:
print "results for class1:"
for class2 in class1.class2_results:
print "results for class2:"
for class3 in class2.class3_results:
print "result"
The generated SQL will look like:
SELECT <Class1 columns>
FROM Class1 LEFT OUTER JOIN Class2 ON <...> LEFT OUTER JOIN Class3 ON
<...>
If you need to join the classes manually (to use Class2 and/or Class3
in an ORDER BY clause, for example), you can use contains_eager to
notify sqlalchemy about those joins:
q = session.query(Class1)
q = q.outerjoin(Class1.class2_results)
q = q.outerjoin(Class2.class3_results)
q = q.options(contains_eager("class2_results"))
q = q.options(contains_eager("class2_results.class3_results"))
for class1 in q:
print "results for class1:"
for class2 in class1.class2_results:
print "results for class2:"
for class3 in class2.class3_results:
print "result"
Hope it helps,
-Conor
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
