SQLAlchemy ORM: modify the columns returned from a query

Question

If I've got an SQLAlchemy ORM query:

admin_users = Session.query(User).filter_by(is_admin=True)

Is it possible to modify the columns returned by that query?

For example, so that I could select only the User.id column, and use that in a sub query:

admin_email_addresses = Session.query(EmailAddress)\
 .filter(EmailAddress.user_id.in_(admin_users.select_columns(User.id))

Note: the .values() method will not work, as it executes the query and returns an iterable of results (so, ex, EmailAddress.user_id.in_(admin_users.values(User.id)) will perform two queries, not one).

I know that I could modify the first query to be Session.query(User.id), but I'm specifically wondering how I could modify the columns returned by a query.

Answer 1

I feel your pain on the values() thing. In 0.6.5 I added with_entities() which is just like values() except doesn't iterate:

q = q.with_entities(User.id)

• trim: For anyone else looking at this post SQlAlchemy 2.0 - Select.with_entities() has been replaced by Select.with_only_columns(): docs.sqlalchemy.org/en/20/core/…

Answer 2

Assuming that your Address.user_id defines a ForeignKey, the query below will do the job more efficiently compared to IN operator:

admin_email_addresses = session.query(EmailAddress).\
 join(User).filter(User.is_admin==True)

If you do not have a ForeignKey (although you should), you can specify the join condition explicitely:

admin_email_addresses = session.query(EmailAddress).\
 join(User, User.id==EmailAddress.user_id).filter(User.is_admin==True)

But if you really would like to do it with in_ operator, here you go (note the subquery):

subq = session.query(User.id).filter(User.is_admin==True).subquery()
admin_email_addresses = session.query(EmailAddress).\
 filter(EmailAddress.user_id.in_(subq)) 

• David Wolever: It's true that, in the example I provided, a JOIN is the correct solution. However, since it doesn't address the question I actually asked, I'm going to be accepting zzzeek's answer.