Hello Connor,
yes that does help indeed, thanks a lot.
My problem is though that i cannot use relation operators because I
use a count over a certain row and group by clauses.
So I kinda had to send my query "manually" but still I cant really get
this stuff done cuz I am not really an SQL wizard.
Greetings, Tom
On Tue, Sep 15, 2009 at 5:30 PM, Conor <conor.edward.da...@gmail.com> wrote:
>
> On Sep 15, 5:38 am, Crusty <crust...@gmail.com> 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 sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
