On Aug 14, 2009, at 8:06 PM, David Bolen wrote:
> > This feels like something that I ought to be able to find already > answered somewhere but I've been searching the archives and the > documentation and haven't been able to discover it. > > Is it possible to construct an ORM query that includes eagerloaded > related objects such that the outer joins due to eagerload options and > regular joins to the same tables in the main query can be > consolidated? two sources of info on this use case: http://www.sqlalchemy.org/trac/wiki/FAQ#ImusinglazyFalsetocreateaJOINOUTERJOINandSQLAlchemyisnotconstructingthequerywhenItrytoaddaWHEREORDERBYLIMITetc.whichreliesupontheOUTERJOIN http://www.sqlalchemy.org/docs/05/mappers.html?highlight=contains_eager#routing-explicit-joins-statements-into-eagerly-loaded-collections good luck ! > > > For example if Session and Period are mapped object classes (to tables > sessions and periods), with a mapper relation "session" defined > between Period and Session, then a query like: > > query(Period).options(eagerload('session')) > > will select from periods with an aliased outer join to sessions in > order > to eager load the related sessions row. > > However, if I need to filter the query based on the related session to > limit the periods returned, and try something like: > > query(Period).join(Session).filter(Session.field == value).\ > options(eagerload('session')) > > then, regardless of using join(Session) or outerjoin(Session), I get > two distinct joins between Period and Session, and does seem to resort > in more database work based on explain output (from PostgreSQL). > > Since I know that I'm going to be doing the join to eager load the > related object, it would be nice if the fields from that join could be > used in the filtering operation. But I haven't been able to figure > out how to get SA to make use of them. > > Is there a way to either construct the initial ORM query so that the > joins can be re-used for eagerloading the associated objects, or > conversely so that the implicit joins created by the eagerload options > can be used to filter the primary query? > > I'm currently using SA 0.5.4p2 > > Thanks. > > -- David > > > > --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---