Lazy loading on column_property in SQLAlchemy
<p>Say I have the following models:</p>

<pre><code>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'))
</code></pre>

<p>Sometimes, when I query departments, I would also like to fetch the number of employees they have. I can achieve this with a <code>column_property</code>, like so:</p>

<pre><code>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
</code></pre>

<p>But then the count is <em>always</em> 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:</p>

<pre><code>Department.query.options(noload(Department.employee_count)).all()
# Exception: can't locate strategy for &lt;class 'sqlalchemy.orm.properties.ColumnProperty'&gt; (('lazy', 'noload'),)
</code></pre>

<p>I've also tried implementing this with a hybrid property instead of a column property:</p>

<pre><code>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))
</code></pre>

<p>With no luck:</p>

<pre><code>Department.query.options(joinedload('employee_count')).all()
# AttributeError: 'Select' object has no attribute 'property'
</code></pre>

<p>I know I can just query the count as a separate entity in the query, but I'd really prefer to have it as an attribute on the model. Is this even possible in SQLAlchemy?</p>

<p><strong>Edit:</strong> 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.</p>

<p>The loading strategies that you tried are for relationships. The loading of a <code>column_property</code> is altered in the same way as normal columns, see <a href="http://docs.sqlalchemy.org/en/latest/orm/loading_columns.html#deferred-column-loading" rel="nofollow">Deferred Column Loading</a>.</p>

<p>You can defer the loading of <code>employee_count</code> by default by passing <code>deferred=True</code> to <code>column_property</code>. When a column is deferred, a select statement is emitted when the property is accessed.</p>

<p><code>defer</code> and <code>undefer</code> from <code>sqlalchemy.orm</code> allow this to be changed when constructing a query:</p>

<pre><code>from sqlalchemy.orm import undefer
Department.query.options(undefer('employee_count')).all()
</code></pre>

python sqlalchemy