SQLAlchemy Order By before Group By

Question

I've researched a lot in terms of doing a query to do an order by before a group by and have found most answers in terms of raw SQL. However, I'd like to see a solution in SQLAlchemy Code.

1. My original naive solution looked as follows:

   session.query(MyTable).order_by(timestamp).group_by(begin_date)
   

   unfortunately, this causes the table to first be grouped then ordered, which will not return what I am expecting.

2. Second,I tried:

    stmt = session.query(MyTable).order_by(timestamp)
    session.query(stmt).group_by(begin_date)
   

   This returns the correct results, however, the results are of KeyedTuples whereas I want to actually have MyTable objects for backwards compatibility reasons.

How can I achieve this?

Answer 1

The code in latest comment of original question seems still have some problem. Here is a working version:

stmt = session.query(MyTable).order_by(timestamp).subquery()
session.query().add_entity(MyTable, alias=stmt).group_by(begin_date);