I have tables:
class Cart(Base):
items = relationship('Item', backref='cart')
class Item(Base):
price = sa.Column(sa.Integer, default=0)
cart_id = sa.Column(
sa.Integer,
sa.ForeignKey(
'Cart.id',
ondelete='CASCADE',
)
)
I want to aggregate sum of cart items price and order carts list by this aggregated sum. How I can build query in SA?
You can implement it as a hybrid property (you have to set also relationship differently by adding lazy='dynamic').
from sqlalchemy import func, select
class Cart(Base):
items = relationship("Item", backref="cart", lazy="dynamic")
@hybrid_property
def items_price(self):
return self.items.with_entities(func.sum(Item.price)).scalar()
@items_price.expression
def items_price(cls):
return (
select(func.sum(Item.price))
.where(Item.cart_id == cls.id)
.label("items_price")
)
class Item(Base):
price = Column(Integer, default=0)
cart_id = Column(
Integer,
ForeignKey(
"Cart.id",
ondelete="CASCADE",
),
)
You can now use items_price in a query:
carts = session.query(Cart).order_by(Cart.items_price.desc())
However, the performance of this approach is not great. So if your db is large, you should try something like that:
cart_price = func.sum(Item.price)
# result query is a list of tuples (<cart_id>, <cart_price>)
carts_price = session.query(
Item.cart_id,
cart_price
).group_by(
Item.cart_id
).order_by(
cart_price.desc()
)
