使用 SQLAlchemy 按关系计数选择时出现自动别名问题 + Postgres

Question

给出以下代码：

from sqlalchemy import Column, ForeignKey, Integer, alias, create_engine, func, select
from sqlalchemy.orm import declarative_base, relationship, sessionmaker

Base = declarative_base()
engine = create_engine(
    "postgresql+psycopg2://***:***@127.0.0.1:5432/***", future=True
)

Session = sessionmaker(engine)


class Foo(Base):
    __tablename__ = "foo"
    id = Column(Integer, primary_key=True)
    bars = relationship("Bar", uselist=True, secondary="foo_bar")
    baz_id = Column(ForeignKey("baz.id"))
    baz = relationship("Baz", back_populates="foos", lazy="joined")


class Bar(Base):
    __tablename__ = "bar"
    id = Column(Integer, primary_key=True)


class Baz(Base):
    __tablename__ = "baz"
    id = Column(Integer, primary_key=True)
    foos = relationship(Foo, uselist=True)


class FooBar(Base):
    __tablename__ = "foo_bar"
    foo_id = Column(ForeignKey(Foo.id), primary_key=True)
    bar_id = Column(ForeignKey(Bar.id), primary_key=True)


Base.metadata.create_all(engine)

stmt = (
    select(Foo)
    .join(FooBar, FooBar.foo_id == Foo.id)
    .group_by(Foo.id)
    .having(func.count(FooBar.foo_id) == 2)
)

Session().execute(stmt)

我想选择所有带有两个 Bar 的 Foo 。

但我遇到了以下错误：

column "baz_1.id" must appear in the GROUP BY clause or be used in an aggregate function

生成的 SQL 是：

SELECT foo.id, foo.baz_id, baz_1.id AS id_1 
FROM foo JOIN foo_bar ON foo_bar.foo_id = foo.id 
LEFT OUTER JOIN baz AS baz_1 ON baz_1.id = foo.baz_id GROUP BY foo.id 
HAVING count(foo_bar.foo_id) = :count_1

现在我明白了 Postgres 想要我做什么，但我不确定如何实现这一点，因为我无法添加 baz_1.id 到 GROUP PY 子句，因为它是 SQLAlchemy 动态生成的东西，我对它没有任何控制权。

Answer 1

由于 Foo 中关系的 lazy='joined' 选项，Baz 被包含在查询中。我们可以在查询中覆盖该选项，以便不执行联接并且查询按需要工作。

stmt = (
    select(Foo)
    .options(orm.lazyload(Foo.baz))  # <- don't automatically join Baz.
    .join(FooBar, FooBar.foo_id == Foo.id)
    .group_by(Foo.id)
    .having(func.count(FooBar.foo_id) == 2)
)

生成的SQL：

SELECT foo.id, foo.baz_id 
FROM foo 
JOIN foo_bar ON foo_bar.foo_id = foo.id 
GROUP BY foo.id 
HAVING count(foo_bar.foo_id) = %(count_1)s