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.