> -----Original Message----- > From: sqlalchemy@googlegroups.com > [mailto:[EMAIL PROTECTED] On Behalf Of John Hunter > Sent: 10 November 2008 15:29 > To: sqlalchemy@googlegroups.com > Subject: [sqlalchemy] Re: select where field=max(field) > > > On Mon, Nov 10, 2008 at 8:53 AM, King Simon-NFHD78 > <[EMAIL PROTECTED]> wrote: > > > It should be fairly easy to build that query with SA's underlying > > expression language. I'm not certain how to do it through > session.query, > > but I'm sure it's possible. > > The snippet you posted does do what I want when inserted directly into > mysql database. I am getting close on the sqlalchemy incantation:: > > > In [106]: q = session.query(Snapshot.strategy, Snapshot.symbol, > sum_pnl).group_by(Snapshot.strategy, > Snapshot.symbol).having(sum_pnl<-15000) > > In [107]: newq = session.query(Snapshot.strategy, func.count('*')) > > In [108]: print newq.select_from (q.subquery > ()).group_by(Snapshot.strategy ) > SELECT anon_1.strategy AS anon_1_strategy, count(%s) AS count_1 > FROM (SELECT snapshot.strategy AS strategy, snapshot.symbol AS > symbol, sum(snapshot.pnl) AS sum_1 > FROM snapshot GROUP BY snapshot.strategy, snapshot.symbol > HAVING sum(snapshot.pnl) < %s) AS anon_1 GROUP BY > snapshot.strategy > > The main problem is that the last "GROUP BY snapshot.strategy" is not > what I want, since I need to be doing "GROUP BY anon_1.strategy". Is > there some label magic I can employ to explicitly refer to the > Snapshot.query from the original "q" rather than the one from "newq"? >
I haven't used session.query to do anything more than simple queries, so I don't honestly know. Does this section of the documentation help at all? http://www.sqlalchemy.org/docs/05/ormtutorial.html#datamapping_joins_ali ases Actually, the section after that (Using Subqueries) probably does something very close to what you want. What's the result of these lines: q1 = (session.query(Snapshot.strategy, Snapshot.symbol, sum_pnl) .group_by(Snapshot.strategy, Snapshot.symbol) .having(sum_pnl<-15000)).subquery() q2 = (session.query(Snapshot.strategy, func.count('*')) .join(q1, Snapshot.strategy = q1.c.strategy) .groupby(Snapshot.strategy)) print q2 Simon --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
