Sqlalchemy: Subquery In From Must Have An Alias

How can I structure this sqlalchemy query so that it does the right thing? I've given everything I can think of an alias, but I'm still getting: ProgrammingError: (psycopg2.Program

Solution 1:

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 
     GROUPBY 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.

Solution 2:

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.