SQLAlchemy：FROM中的子查询必须具有别名 [英] Sqlalchemy: subquery in FROM must have an alias

问题描述

如何构造此sqlalchemy查询，以便其执行正确的操作？

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.ProgrammingError) subquery in FROM must have an alias
LINE 4: FROM (SELECT foo.id AS foo_id, foo.version AS ...

此外，正如IMSoP指出的那样似乎正在尝试将其转换为交叉联接，但我只希望它通过同一查询的子查询将一个表与一个组联接。

Also, as IMSoP pointed out, it seems to be trying to turn it into a cross join, but I just want it to join a table with a group by subquery on that same table.

这里是sqlalchemy ：

Here is the sqlalchemy:

（注意：我已将其重写为一个尽可能完整的独立文件，可以从python shell运行）

from sqlalchemy import create_engine, func, select
from sqlalchemy import Column, BigInteger, DateTime, Integer, String, SmallInteger
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

engine = create_engine('postgresql://postgres:#######@localhost:5435/foo1234')
session = sessionmaker()
session.configure(bind=engine)
session = session()

Base = declarative_base()

class Foo(Base):
     __tablename__ = 'foo'
     __table_args__ = {'schema': 'public'}
     id = Column('id', BigInteger, primary_key=True)
     time = Column('time', DateTime(timezone=True))
     version = Column('version', String)
     revision = Column('revision', SmallInteger)

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 = select([
    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
).alias('foo_q')

thing = session.query(foo_q).all()
print thing

生成的sql：

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

和这是玩具桌

CREATE TABLE foo (
id bigint ,
time timestamp with time zone,
version character varying(32),
revision smallint
);

我期望得到的SQL（期望的SQL）将是这样的：

The SQL was I expecting to get (desired SQL) would be something like this:

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(time) AS foo_max_time,
            id AS foo_id GROUP BY id
            ) AS foo_max_time_q
        ON foo_max_time_q.foo_id = foo.id

最后注：
我m希望在可能的情况下使用select（）而不是session.query（）获得答案。谢谢

Final note: I'm hoping to get an answer using select() instead of session.query() if possible. Thank you

推荐答案

您快到了。进行 selectable 子查询，然后通过join()：

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__())

打印（手动添加）：

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.

这篇关于SQLAlchemy：FROM中的子查询必须具有别名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！