selecting whole entities from SQLAlchemy subqueries

Question

If I specify mapped classes (~= database tables) in a SQLAlchemy query, then the returned rows will contain instances of those classes:

q = sess.query(table1, table2, table3.string_column)
q.first()
==> ( <instance of table1>,
      <instance of table2>,
      'string' )

However, if I select from a subquery, then the returned rows contain the individual columns rather than the class instances:

q = sess.query(table1, table2, table3.string_column)

q2 = sess.query( q.subquery() )
q2.first()
==> ( col1_of_table1, col2_of_table1, ...,
      col2_of_table2, col2_of_table2, ...,
      'string' )

Is there a way to specify that I want to preserve the row from the subquery as an instance of a mapped class?

I can't figure out how to do this without joining to a new instance of the mapped classes. The corresponding_column method allows me to refer to specific columns from the subquery, but I can't figure out how to refer to complete entities from the subquery. I've tried playing around with select_from but it doesn't give me the right behavior.

Any suggestions?

Answer 1

the query that returns entities always needs to be told about those entities - thats the things it wants to "select". The source of rows is what it wants to select "from". Two separate things.

so given:

q = sess.query(table1, table2, table3.string_column)
q = q.subquery()

once you call subquery(), the ORM-ness of the query is mostly gone, it's just a SELECT object. To select entities from that you have to name them out again, and use select_entity_from (which if you are actually on 0.7, is what select_from() was back then; you'd need to be specific about "not the right behavior):

q2 = sess.query(table1, table2, table3.string_column).select_entity_from(q)