On Jan 21, 12:12 am, Michael Bayer <[EMAIL PROTECTED]> wrote:
> 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
> > sectionhttp://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)
Yes, this works correctly for me now, thanks!
>
> 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']))
Yes, this would be nice..
Another option to give access to the aliases, (off the top of my
head):
Address.aliased_columns[0].email_address >
Address.aliased_columns[1].email_address
Eoghan
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
