SQLAlchemy-Utils provides way of automatically calculating aggregate values of related models and saving them to parent model.
This solution is inspired by RoR counter cache, counter_culture and stackoverflow reply by Michael Bayer.
Many times you may have situations where you need to calculate dynamically some aggregate value for given model. Some simple examples include:
Now all these aggregates can be elegantly implemented with SQLAlchemy column_property function. However when your data grows calculating these values on the fly might start to hurt the performance of your application. The more aggregates you are using the more performance penalty you get.
This module provides way of calculating these values automatically and efficiently at the time of modification rather than on the fly.
from sqlalchemy_utils import aggregated
class Thread(Base):
__tablename__ = 'thread'
id = sa.Column(sa.Integer, primary_key=True)
name = sa.Column(sa.Unicode(255))
@aggregated('comments', sa.Column(sa.Integer))
def comment_count(self):
return sa.func.count('1')
comments = sa.orm.relationship(
'Comment',
backref='thread'
)
class Comment(Base):
__tablename__ = 'comment'
id = sa.Column(sa.Integer, primary_key=True)
content = sa.Column(sa.UnicodeText)
thread_id = sa.Column(sa.Integer, sa.ForeignKey(Thread.id))
thread = Thread(name=u'SQLAlchemy development')
thread.comments.append(Comment(u'Going good!'))
thread.comments.append(Comment(u'Great new features!'))
session.add(thread)
session.commit()
thread.comment_count # 2
Aggregate expression can be virtually any SQL expression not just a simple function taking one parameter. You can try things such as subqueries and different kinds of functions.
In the following example we have a Catalog of products where each catalog knows the net worth of its products.
from sqlalchemy_utils import aggregated
class Catalog(Base):
__tablename__ = 'catalog'
id = sa.Column(sa.Integer, primary_key=True)
name = sa.Column(sa.Unicode(255))
@aggregated('products', sa.Column(sa.Integer))
def net_worth(self):
return sa.func.sum(Product.price)
products = sa.orm.relationship('Product')
class Product(Base):
__tablename__ = 'product'
id = sa.Column(sa.Integer, primary_key=True)
name = sa.Column(sa.Unicode(255))
price = sa.Column(sa.Numeric)
catalog_id = sa.Column(sa.Integer, sa.ForeignKey(Catalog.id))
Now the net_worth column of Catalog model will be automatically whenever:
from decimal import Decimal
product1 = Product(name='Some product', price=Decimal(1000))
product2 = Product(name='Some other product', price=Decimal(500))
catalog = Catalog(
name=u'My first catalog',
products=[
product1,
product2
]
)
session.add(catalog)
session.commit()
session.refresh(catalog)
catalog.net_worth # 1500
session.delete(product2)
session.commit()
session.refresh(catalog)
catalog.net_worth # 1000
product1.price = 2000
session.commit()
session.refresh(catalog)
catalog.net_worth # 2000
Sometimes you may need to define multiple aggregate values for same class. If you need to define lots of relationships pointing to same class, remember to define the relationships as viewonly when possible.
from sqlalchemy_utils import aggregated
class Customer(Base):
__tablename__ = 'customer'
id = sa.Column(sa.Integer, primary_key=True)
name = sa.Column(sa.Unicode(255))
@aggregated('orders', sa.Column(sa.Integer))
def orders_sum(self):
return sa.func.sum(Order.price)
@aggregated('invoiced_orders', sa.Column(sa.Integer))
def invoiced_orders_sum(self):
return sa.func.sum(Order.price)
orders = sa.orm.relationship('Order')
invoiced_orders = sa.orm.relationship(
'Order',
primaryjoin=
'sa.and_(Order.customer_id == Customer.id, Order.invoiced)',
viewonly=True
)
class Order(Base):
__tablename__ = 'order'
id = sa.Column(sa.Integer, primary_key=True)
name = sa.Column(sa.Unicode(255))
price = sa.Column(sa.Numeric)
invoiced = sa.Column(sa.Boolean, default=False)
customer_id = sa.Column(sa.Integer, sa.ForeignKey(Customer.id))
Aggregate expressions also support many-to-many relationships. The usual use scenarios includes things such as:
user_group = sa.Table('user_group', Base.metadata,
sa.Column('user_id', sa.Integer, sa.ForeignKey('user.id')),
sa.Column('group_id', sa.Integer, sa.ForeignKey('group.id'))
)
class User(Base):
__tablename__ = 'user'
id = sa.Column(sa.Integer, primary_key=True)
name = sa.Column(sa.Unicode(255))
@aggregated('groups', sa.Column(sa.Integer, default=0))
def group_count(self):
return sa.func.count('1')
groups = sa.orm.relationship(
'Group',
backref='users',
secondary=user_group
)
class Group(Base):
__tablename__ = 'group'
id = sa.Column(sa.Integer, primary_key=True)
name = sa.Column(sa.Unicode(255))
user = User(name=u'John Matrix')
user.groups = [Group(name=u'Group A'), Group(name=u'Group B')]
session.add(user)
session.commit()
session.refresh(user)
user.group_count # 2
Aggregates can span accross multiple relationships. In the following example each Catalog has a net_worth which is the sum of all products in all categories.
from sqlalchemy_utils import aggregated
class Catalog(Base):
__tablename__ = 'catalog'
id = sa.Column(sa.Integer, primary_key=True)
name = sa.Column(sa.Unicode(255))
@aggregated('categories.products', sa.Column(sa.Integer))
def net_worth(self):
return sa.func.sum(Product.price)
categories = sa.orm.relationship('Product')
class Category(Base):
__tablename__ = 'category'
id = sa.Column(sa.Integer, primary_key=True)
name = sa.Column(sa.Unicode(255))
catalog_id = sa.Column(sa.Integer, sa.ForeignKey(Catalog.id))
products = sa.orm.relationship('Product')
class Product(Base):
__tablename__ = 'product'
id = sa.Column(sa.Integer, primary_key=True)
name = sa.Column(sa.Unicode(255))
price = sa.Column(sa.Numeric)
category_id = sa.Column(sa.Integer, sa.ForeignKey(Category.id))
from sqlalchemy_utils import aggregated
class Movie(Base):
__tablename__ = 'movie'
id = sa.Column(sa.Integer, primary_key=True)
name = sa.Column(sa.Unicode(255))
@aggregated('ratings', sa.Column(sa.Numeric))
def avg_rating(self):
return sa.func.avg(Rating.stars)
ratings = sa.orm.relationship('Rating')
class Rating(Base):
__tablename__ = 'rating'
id = sa.Column(sa.Integer, primary_key=True)
stars = sa.Column(sa.Integer)
movie_id = sa.Column(sa.Integer, sa.ForeignKey(Movie.id))
movie = Movie('Terminator 2')
movie.ratings.append(Rating(stars=5))
movie.ratings.append(Rating(stars=4))
movie.ratings.append(Rating(stars=3))
session.add(movie)
session.commit()
movie.avg_rating # 4
Decorator that generates an aggregated attribute. The decorated function should return an aggregate select expression.
Parameters: |
|
---|