ORM Join with explicit Alias

[Eoghan Murray]

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=='ja...@google.com').\
... join('addresses',
aliased=True).filter(Address.email_address=='j...@yahoo.com')

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?

Thanks,

Eoghan

[Michael Bayer]

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']))

[Eoghan Murray]

On Jan 21, 12:12 am, Michael Bayer <mike...@zzzcomputing.com> 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=='j...@google.com').\
> > ... join('addresses',
> > aliased=True).filter(Address.email_address=='...@yahoo.com')

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