SQLAlchemy join with subquery and multiple mappers

According to the SQLAlchemy documentation one can do this:

subq = (select(Address)
    .where(Address.email_address == "ed@foo.com")
    .subquery()
)
address_subq = aliased(Address, subq, name="address")
q = select(User).join(address_subq, User.addresses)

SQLA docs, section "Advanced Targeting"

What I would want from the query is not the User but a tuple of (User, Address) constructed via a "left outer join" so I get tuples of (User, Address) or (User, None) if the user has no address or the (subquery?) filter removed the address from the users list of addresses if they don't match the filter's criteria.

subq = (select(Address)
    .where(Address.email_address == 'ed@foo.com')
    .subquery()
)
address_subq = aliased(Address, subq, name="address")
q = select(User, Address).outerjoin(address_subq, User.addresses)

I adapted to example to my actual classes, but it does not work. I have tried with "correlated" and "aliased", but I do find the solution. What do I need to change to make it work?

This would be the SQL I am after:

SELECT
   users.name, addr.email_address
FROM
   users
      LEFT OUTER JOIN
   (SELECT
      address.email_address, address.user_id
   FROM
      address
   WHERE
      address.email_address LIKE '%foo.com%') AS addr ON users.id = addr.user_id

_{This question has been updated for SQLAlchemy 2.0 to reflect the 2.0 style while maintaining the original intent as of this revision which was written in the 1.x style.}

Solution

_{This answer has been updated for SQLAlchemy 2.0 to reflect the 2.0 style while maintaining the original intent of this prior revision which was written in the 1.x style.}

If you want specific columns (prior to the edit that included the intended statement, it wasn't clear what you meant by a tuple of (User, Address), as that implies the entire class which is what is being returned already), simply pass the desired attributes to select as documented in the ORM querying guide.

For your intended part, you need to make use of the subquery's email address attribute as the query parameter instead of the main Address class, and with manual joins as specified again in the tutorial under the joining to subqueries section.

The address_subq in your question can be used as is, but if you want to replace your inner query to be more like the intended subquery, try:

subq = (select(Address.email_address, Address.user_id)
    .where(Address.email_address.like('%@foo.com'))
    .subquery()
)
address_subq = aliased(Address, subq, name="address")

The final query that satisfies your requirement will be:

q = (select(User.name, address_subq.email_address)
    .outerjoin(address_subq, User.id==address_subq.user_id)
)

Printing and executing the query (the print output has been modified for readability)

>>> print(q)
SELECT users.name, anon_1.email_address 
FROM users LEFT OUTER JOIN (
    SELECT address.email_address AS email_address,
           address.user_id AS user_id 
    FROM address 
    WHERE address.email_address LIKE :email_address_1
) AS anon_1 ON users.id = anon_1.user_id
>>> with Session(engine) as session:
...     print(session.execute(q).all())
[('ed', 'ed@foo.com'), ('wendy', None), ('mary', None), ('fred', None), ('jack', None)]