2

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?

esnadr
  • 427
  • 3
  • 18
user1477510
  • 63
  • 6
  • With every database engine I've used, having order by before group by throws an error. What answers did you find? – Dan Bracuk Mar 18 '14 at 02:24
  • Can you show the SQL you're trying to produce? do you maybe want `... group by begin_date order by begin_date, timestamp`? – SingleNegationElimination Mar 18 '14 at 03:06
  • I figured it out but can't post an answer to my own question, so here's the answer: stmt = session.query(MyTable).order_by(timestamp); session.query().add_entity(MyTable, alias=stmt).group_by(begin_date); – user1477510 Mar 18 '14 at 03:21

1 Answers1

2

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);
Elias Soong
  • 21
  • 2