On Jan 20, 2008, at 6:52 PM, Eoghan Murray wrote:
> > Hi All, > > I wish to do an aliased join similar to the last example in the > section http://www.sqlalchemy.org/docs/04/ormtutorial.html#datamapping_joins > >>>> session.query(User).\ > ... join('addresses', > aliased=True).filter(Address.email_address=='[EMAIL PROTECTED]').\ > ... join('addresses', > aliased=True).filter(Address.email_address=='[EMAIL PROTECTED]') > > Except that I want to provide my own Alias for the Address table so I > can compare fields from the two aliased address tables, e.g. > >>>> Address_1 = Address.table.alias() >>>> Address_2 = Address.table.alias() >>>> session.query(User).\ > ... join(Address_1).\ > ... join(Address_2).\ > ... filter(Address_1.email_address>Address_2.email_address) > > This fails because the 'join' function above expects a property to > join on, rather than a table or table alias. > > > I've tried to transform it into a select_from query: > >>>> User >>>> .query.select_from(User.table.join(Address_1).join(Address_2))./ > ... filter(Address_1.email_address>Address_2.email_address) > > but then you lose the ability to add_entities; the following doesn't > work: > >>>> User >>>> .query >>>> .add_entity >>>> (Address_1 >>>> ).select_from(User.table.join(Address_1).join(Address_2))./ > ... filter(Address_1.email_address>Address_2.email_address) > > Any ideas on how to do this correctly? > you're almost there; add_entity has an "alias" argument: session.query(User).\ select_from(users.join(Address_1).join(Address_2)).\ filter(Address_1.c.email_address>Address_2.c.email_address).\ add_entity(Address, alias=Address_1) Also, you can't use it here since you are comparing between the two address aliases, but in general you can also create aliases with join() using join('addresses', aliased=True); subsequent filter() criterion using the plain Address.table will be adapted to the alias used in the most recent join. This would allow you to use the Address class as a base for filter criterion also. It also would be quite easy for us to add a helper option here to join(), such that you could say query.join('addresses', with_aliases=[Address_1])...I was thinking of that just today. (with_aliases is a list to support query.join(['foo','bar', 'bat'])) --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
