'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