Say I have the following models:
class Department(Base):
__tablename__ = 'departments'
id = Column(Integer, primary_key=True)
class Employee(Base):
__tablename__ = 'employees'
id = Column(Integer, primary_key=True)
department_id = Column(None, ForeignKey(Department.id), nullable=False)
department = relationship(Department, backref=backref('employees'))
Sometimes, when I query departments, I would also like to fetch the number of employees they have. I can achieve this with a column_property, like so:
Department.employee_count = column_property(
select([func.count(Employee.id)])
.where(Employee.department_id == Department.id)
.correlate_except(Employee)
)
Department.query.get(1).employee_count # Works
But then the count is always fetched via a subquery, even when I don't need it. Apparently I can't ask SQLAlchemy not to load this at query time, either:
Department.query.options(noload(Department.employee_count)).all()
# Exception: can't locate strategy for <class 'sqlalchemy.orm.properties.ColumnProperty'> (('lazy', 'noload'),)
I've also tried implementing this with a hybrid property instead of a column property:
class Department(Base):
#...
@hybrid_property
def employee_count(self):
return len(self.employees)
@employee_count.expression
def employee_count(cls):
return (
select([func.count(Employee.id)])
.where(Employee.department_id == cls.id)
.correlate_except(Employee)
)
With no luck:
Department.query.options(joinedload('employee_count')).all()
# AttributeError: 'Select' object has no attribute 'property'
I know I can just query the count as a separate entity, but I need it often enough that I'd really prefer the convenience of having it as an attribute on the model. Is this even possible in SQLAlchemy?
Edit: To clarify, I want to avoid the N+1 problem and have the employee count get loaded in the same query as the departments, not in a separate query for each department.
