Sqlalchemy: subquery in FROM must have an alias

You are almost there. Make a "selectable" subquery and join it with the main query via join():

foo_max_time_q = select([func.max(Foo.time).label('foo_max_time'),                         Foo.id.label('foo_id')                        ]).group_by(Foo.id                         ).alias("foo_max_time_q")foo_q = session.query(          Foo.id.label('foo_id'),          Foo.version.label('foo_version'),          Foo.revision.label('foo_revision'),          foo_max_time_q.c.foo_max_time.label('foo_max_time')                ).join(foo_max_time_q,                        foo_max_time_q.c.foo_id == Foo.id)print(foo_q.__str__())

Prints (prettified manually):

SELECT     foo.id AS foo_id,     foo.version AS foo_version,     foo.revision AS foo_revision,     foo_max_time_q.foo_max_time AS foo_max_time FROM     foo JOIN     (SELECT          max(foo.time) AS foo_max_time,          foo.id AS foo_id      FROM          foo      GROUP BY foo.id) AS foo_max_time_q ON     foo_max_time_q.foo_id = foo.id

The complete working code is available in this gist.

Cause

subquery in FROM must have an alias

This error means the subquery (on which we're trying to perform a join) has no alias.
Even if we .alias('t') it just to satisfy this requirement, we will then get the next error:

missing FROM-clause entry for table "foo"

That's because the join on clause (... == Foo.id) is not familiar with Foo.
It only knows the "left" and "right" tables: t (the subquery) and foo_max_time_q.

Solution

Instead, select_from a join of Foo and foo_max_time_q.

Method 1

Replace .join(B, on_clause) with .select_from(B.join(A, on_clause):

]).join(foo_max_time_q, foo_max_time_q.c.foo_id == Foo.id

]).select_from(foo_max_time_q.join(Foo, foo_max_time_q.c.foo_id == Foo.id)

This works here because A INNER JOIN B is equivalent to B INNER JOIN A.

Method 2

To preserve the order of joined tables:

from sqlalchemy import join

and replace .join(B, on_clause) with .select_from(join(A, B, on_clause)):

]).join(foo_max_time_q, foo_max_time_q.c.foo_id == Foo.id

]).select_from(join(Foo, foo_max_time_q, foo_max_time_q.c.foo_id == Foo.id)

Alternatives to session.query() can be found here.