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. As always thanks a lot for your help Werner --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---