Sqlalchemy Orm: Sum Of Products

Let's say I have a ROOMS table with width and length columns, and a corresponding SQLAlchemy model. Is there a clean and efficient way to get a total area of all rooms, i.e. sum(le

Solution 1:

The more interesting part about your question is solving the greatest-n-per-group problem. Now, I'm pretty green when it comes to MySQL, so there might be more efficient solutions to that than this:

In [43]: price_alias = db.aliased(Price)

In [44]: latest_price = db.session.query(Price.commodity_id, Price.amount).\
    ...:     outerjoin(price_alias,
    ...:               db.and_(price_alias.commodity_id == Price.commodity_id,
    ...:                       price_alias.time > Price.time)).\
    ...:     filter(price_alias.id == None).\
    ...:     subquery()

The self left join tries to join rows with greater time, which do not exist for the latest price, hence the filter(price_alias.id == None).

What's left then is to just join Holdings with the subquery:

In [46]: sum_of_products = db.session.query(
    ...:         db.func.sum(Holding.quantity * latest_price.c.amount)).\
    ...:     join(latest_price,
    ...:          latest_price.c.commodity_id == Holding.commodity_id).\
    ...:     scalar()

Solution 2:

Assuming your model is named Room and it has properties such as length and width:

from sqlalchemy importfunc
total_area = session.query(func.sum(Room.length * Room.width))

Which is going to be translated as something like that:

SELECTsum(rooms.length * rooms.width) AS sum_1 
FROM rooms