SQLAlchemy - How can I make eager loading count property

python, sqlalchemy

Solution

I solved it by using `sqlalchemy.orm.column_property`

I replaced the `foo_count` by following

import sqlalchemy as s, func, select
from sqlalchemy.orm import relationship, column_property

# ...

class Foo(Base):
    __tablename__ = 'foo'
    id = s.Column(s.Integer, primary_key=True)
    bar_id = s.Column(s.Integer, s.ForeignKey('bar.id'))
    bar = relationship('Bar')


class Bar(Base):
    __tablename__ = 'bar'
    id = s.Column(s.Integer, primary_key=True)

    foo_count = column_property(
        select([func.count(Foo.id)])
        .where(Foo.bar_id == id)
    )

Problem

I want to make a property for model which contains count. Since I always need the property, I want to make query with `JOIN` like `sqlalchemy.orm.relationship` with `lazy='joined'` For example, I defined models like following ``` import sqlalchemy as s, func from sqlalchemy.orm import relatioship # ... class Foo(Base): __tablename__ = 'foo' id = s.Column(s.Integer, primary_key=True) bar_id = s.Column(s.Integer, s.ForeignKey('bar.id')) bar = relationship('Bar') class Bar(Base): __tablename__ = 'bar' id = s.Column(s.Integer, primary_key=True) @property def foo_count(self): return Foo.query.filter_by(bar=self).count() ``` When I access to property `foo_count`, then it will send query to DBMS. Since I always access to this property, I want to load it eagerly the counting property like this ``` # Not session.query(Bar, func.count(Foo.id)).join(Foo) ... bar = Bar.query.first() ``` SQL will be like this ``` SELECT id, COUNT(Foo.id) FROM bar INNER JOIN foo ON bar.id = foo.id ``` Then `bar.foo_count` will not occur SQL query. How can I make a property like `foo_count`?

Original source