On Sep 12, 2008, at 11:43 AM, Werner F. Bruhin wrote:
> > Michael, > > Michael Bayer wrote: >> ... >> if CellarBook, Cbvintage, etc. are mapped classes, the join and >> outerjoin functions you must be using are "from sqlalchemy.orm import >> join, outerjoin". those are aware of ORM mapped classes whereas >> sqlalchemy.sql.expression.join/outerjoin are not. >> >> You can use the outerjoin() attached to Qeury for the whole thing, >> i.e.: >> >> query(Class1).outerjoin(Class2, Class3) >> >> if the ON condition is required: >> >> query(Class1).outerjoin((Class2, Class1.foo==Class2.bar), (Class3, >> Class3.bar==Class2.foo)) >> > O.K. that looks easy, and I tried this before but I don't get the > result I am looking for. > > wines = session.query(db.Cellarbook).outerjoin(db.Cbvintage, > db.Cbbottle) > > print wines # if I use this sql select in my db ide I get 8 rows > > for wine in wines.all(): > print wine > print '\n' > > If I use the generated SQL I get 8 rows, but in my for loop above I > only > get 5. > > i.e. I get the following: (note the integer at the end is the > dbCellarbook.primarykey, so this "duplicated" rows have more then one > row in cbvintage and possible in cbbottle). > Glen Elgin Virgin Oak Glen Elgin Virgin Oak 141 > Ardbeg Renaissance Ardbeg Renaissance 142 > Ch. St. Georges Ch. St. Georges 144 > Ch. St. Georges Ch. St. Georges 144 > Ch. St. Georges Ch. St. Georges 144 > Goldwater Esslin Goldwater Esslin, Merlot 145 > Goldwater Esslin Goldwater Esslin, Merlot 145 > Goldwater Zell Goldwater Zell 146 > > Maybe I am asking the question incorrectly. > > In other words: > db.Cellarbook (a wine) > - relates (oneToMany) to db.Cbvintage (zero or more vintages) > - which in turn relates (oneToMany) to db.Cbbottle (zero or more > bottle > sizes) > > What do I need to do to get the 8 rows in my for loop? Can I do this > with just a query or do I need to look into other things. thats actually working correctly. When you say sess.query(SomeClass), the Query will load rows each representing a distinct SomeClass instance based on the primary key. The outerjoins used result in the same SomeClass primary key being repeated, but since those aren't being returned, query() returns just the five unique SomeClass instances. Now, if you actually want to get back objects for the outerjoins, youd say somehting like: sess.query(SomeClass, SomeOtherClass, SomeThirdClass) this will disable the uniquing logic used for a single class and return tuples containining instances of the above three classes, exactly corresponding to the full result of the outerjoin. Primary keys which aren't fulfilled for the joined classes will correspoind to an object value of None. --~--~---------~--~----~------------~-------~--~----~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
