SQLAlchemy - How can I make eager loading count pr

问题:

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?

回答1:

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

回答2:

Please take a look at the Hybrid Attribute extension.

Your object model will look similar to the below:

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

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

    @hybrid_property
    def foo_count(self):
        return object_session(self).query(Foo).filter(Foo.bar==self).count()

    @foo_count.expression
    def foo_count(cls):
        return select([func.count(Foo.id)]).where(Foo.bar_id == cls.id).label('foo_count')

foo_count will not be eagerly loaded, but you can use it in queries like below (both in SELECT and in WHERE clause:

qry = session.query(Bar, Bar.foo_count).filter(Bar.foo_count > 0)
for (bar, bar_foo_count) in qry:
    print bar, bar_foo_count

As you can see, the query will return tuples of (Bar, foo_count) in just one query, and now you can do what you wish with that.