Complete worked example
python
from sqlalchemy import create_engine, event, ForeignKey, select
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship, Session
class Base(DeclarativeBase):
pass
class Employer(Base):
__tablename__ = "employers"
id: Mapped[int] = mapped_column(primary_key=True)
roles: Mapped[list["Role"]] = relationship(back_populates="employer")
class Role(Base):
__tablename__ = "roles"
id: Mapped[int] = mapped_column(primary_key=True)
employer_id: Mapped[int] = mapped_column(ForeignKey("employers.id", ondelete="RESTRICT"))
employer: Mapped[Employer] = relationship(back_populates="roles")
from sqlalchemy import func
def inspect_example(counts):
engine = create_engine("sqlite+pysqlite://", connect_args={"autocommit": False})
try:
Base.metadata.create_all(engine)
with Session(engine) as session, session.begin():
for i, count in enumerate(counts, 1):
session.add(Employer(id=i, roles=[Role() for _ in range(count)]))
with Session(engine) as session:
statement = (select(Employer.id, func.count(Role.id))
.outerjoin(Employer.roles).group_by(Employer.id).order_by(Employer.id))
return [list(row) for row in session.execute(statement)]
finally:
engine.dispose()- Reasoning
- The statement selects Employer.id and count(Role.id), outer-joins the relationship, groups by the selected parent identity and orders by that identity. COUNT over the child primary key ignores the NULL placeholder. For counts [2, 0, 1], the correct report is [[1, 2], [2, 0], [3, 1]]. The selected nonaggregate expression is explicitly part of GROUP BY instead of relying on SQLite accepting an under-specified grouped SELECT. The returned rows are ordinary data. Their stable order is checked independently of the group computation, so an accidental row-order assumption cannot masquerade as a correct aggregate.