python - Sqlalchemy ORM group by twice / subquery in a single statement

Question

I have the following SQL statement that I'm trying to convert into a sqlalchemy ORM query:

select name, max(pnl) from 
    (
       select name, filedate, sum(pnl) as pnl from portfolio 
       where filedate>="2020-01-01" 
       group by filedate, name
    ) maxg 
group by name

I can't seem to convert this into an ORM query, this is what I have tried so far, building the inner subquery, but I can't seem to construct the ORM query for the outer query. Is there a way to alias the inner subquery so that I can continue to reference the fields?

from sqlalchemy import func

session.query(
    Entity.name, Entity.filedate, func.sum(Entity.pnl).label("pnl"))
       .filter(Entity.filedate >= datetime.date(2020,1,1))
       .group_by(Entity.filedate, Entity.name)
)

question from:https://stackoverflow.com/questions/66059558/sqlalchemy-orm-group-by-twice-subquery-in-a-single-statement

Answer 1

In case anyone had the same question, this solved the issue for me, albeit an ugly implementation. A subquery can be created using subquery(), and the columns accessed via subquery.c.{columnname}

# Subquery
sq = session.query(
    Entity.name, Entity.filedate, func.sum(Entity.pnl).label("pnl"))
       .filter(Entity.filedate >= datetime.date(2020,1,1))
       .group_by(Entity.filedate, Entity.name)
).subquery()

session.query(sq.c.name, func.max(sq.c.pnl))
    .group_by(sq.c.name).all()