'Auto-aliasing issues when selecting by relationship count with SQLAlchemy + Postgres
Given the following code:
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)
I want to select all Foos with exactly two Bars.
But I'm running into the following error:
column "baz_1.id" must appear in the GROUP BY clause or be used in an aggregate function
The generated SQL is:
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
Now I get what Postgres wants me to do, but I'm not sure how to achieve this, since I can't add baz_1.id to the GROUP PY clause because it's something that SQLAlchemy generates on the fly and I don't have any control over it.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
