On Sep 16, 3:38 am, Crusty <[email protected]> wrote:
> 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
I would still try to use just one SQL query for performance. I'm
assuming your manual query provides results in (class1, class2,
class3) format. I see two approaches:
1. This assumes you only need to iterate over the result set once.
Ensure your query is ordered by (class1.id, class2.id) or whichever
columns you are using to represent class1 and class2 in your resultset
above. Then use itertools.groupby to group the results by class1, and
then again by class2. Example:
# Assume results contains tuples of (class1, class2, class3).
for (class1, class1_results) in itertools.groupby(results, lambda
result: result[0]):
print "results for class1:", class1
for (class2, class2_results) in itertools.groupby(class1_results,
lambda result: result[1]):
print "results for class2:", class2
for class3 in (result[2] for result in class2_results):
print "result", class3
2. Use dictionaries to track nested results. This is more
straightforward and good for repeated iteration, but requires loading
all of the results into memory.
class1_to_class2s = {}
class2_to_class3s = {}
for (class1, class2, class3) in results:
class2s = class1_to_class2s.setdefault(class1, [])
# Watch out for nulls in case you are using outer joins.
if class2 is not None:
class2s.append(class2)
class3s = class2_to_class3s.setdefault(class2, [])
if class3 is not None:
class3s.append(class3)
for (class1, class2s) in class1_to_class2s.iteritems():
print "result for class1:", class1
for (class2, class3s) in class2_to_class3s.iteritems():
print "result for classs2:", class2
for class3 in class3s:
print "result:", class3
If this still doesn't work for you, please post a more detailed model
(including id columns and result columns) and query (including count
and group by clauses). Hopefully that will make it easier for us to
see your intent.
-Conor
> On Tue, Sep 15, 2009 at 5:30 PM, Conor <[email protected]> wrote:
>
> > 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
-~----------~----~----~----~------~----~------~--~---
