SQLAlchemy - How can I get the load load count property

Question

I want to create a property for a model that contains count.

Since I always need a property, I want to make a query with JOIN as sqlalchemy.orm.relationship with lazy='joined'

For example, I defined models like the 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 the foo_count property, it will send a request to the DBMS.

Since I always access this property, I want to load its counting property with confidence

 # Not session.query(Bar, func.count(Foo.id)).join(Foo) ... bar = Bar.query.first() 

SQL will look like this

 SELECT id, COUNT(Foo.id) FROM bar INNER JOIN foo ON bar.id = foo.id 

Then bar.foo_count will not execute the SQL query.

How to create a property of type foo_count ?

Answer 1

I solved this using sqlalchemy.orm.column_property

I replaced foo_count as follows

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

Answer 2

Check out the Hybrid Attribute extension.

Your object model will look something like this:

 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 load, but you can use it in queries as shown below (both in SELECT and in WHERE :

 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 (Bar, foo_count) only one query, and now you can do what you want.