说我有以下型号：

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'))

有时,当我查询部门时,我还想获取他们拥有的员工数量.我可以使用column_property实现这一点,如下所示：

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

但是,即使我不需要,也总是通过子查询获取计数.显然我不能要求sqlAlchemy在查询时不加载它：

Department.query.options(noload(Department.employee_count)).all()
# Exception: can't locate strategy for sqlalchemy.orm.properties.ColumnProperty'> (('lazy','noload'),)

我也尝试使用混合属性而不是列属性来实现它：

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))

没有运气：

Department.query.options(joinedload('employee_count')).all()
# AttributeError: 'Select' object has no attribute 'property'

我知道我可以在查询中查询计数作为一个单独的实体,但我真的更喜欢将它作为模型的属性.这在sqlAlchemy中甚至可能吗？

编辑：为了澄清,我想避免N 1问题并将员工数量加载到与部门相同的查询中,而不是在每个部门的单独查询中.