On Fri, Nov 7, 2008 at 3:57 PM, Michael Bayer <[EMAIL PROTECTED]> wrote:
>
> Theres a good tutorial on the topic of GROUP BY from a SQL
> perspective, here:
>
> http://weblogs.sqlteam.com/jeffs/jeffs/archive/2007/07/20/60261.aspx
>
> in this case you probably want
> query.filter(Snapshot.totalqty==func.max(Snapshot.totalqty).select()).
Indeed, that does work:
session.query(Snapshot.symbol).filter(Snapshot.datetime==func.max(Snapshot.datetime).select()).all()
and I read the tutorial you pointed me an certainly get the idea that
everything in the select list must be contained in the group by clause
or used in an aggregate function, but I am still unclear on what the
role of the "select" method is on the filter clause above, and how it
pertains to the tutorial message.
I'm now having a related problem that perhaps will shed light on my
confusions...
Here is a query that lists the sum(pnl) for each symbol and strategy
in my snapshots table
session.query(Snapshot.strategy, Snapshot.symbol,
func.sum(Snapshot.pnl)).group_by(Snapshot.strategy,
Snapshot.symbol).all()
That works fine. But what if I only want to list the rows where the
sum(pnl)<-15000 ? I tried a few things:
session.query(Snapshot.strategy, Snapshot.symbol,
func.sum(Snapshot.pnl)).group_by(Snapshot.strategy,
Snapshot.symbol).filter(func.sum(Snapshot.pnl)<-15000).all()
but got the error below
raise exc.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
ProgrammingError: (ProgrammingError) (1111, 'Invalid use of group
function') u'SELECT snapshot.strategy AS snapshot_strategy,
snapshot.symbol AS snapshot_symbol, sum(snapshot.pnl) AS sum_1 \nFROM
snapshot \nWHERE sum(snapshot.pnl) < %s GROUP BY snapshot.strategy,
snapshot.symbol' [-15000]
So I thought I'd try your "select" magic method, which as noted above
I don't really understand how to use. But I get a new error when I
call the select method on the sum func, eg
In [47]: session.query(Snapshot.strategy, Snapshot.symbol,
func.sum(Snapshot.pnl)).group_by(Snapshot.strategy,
Snapshot.symbol).filter(-15000>func.sum(Snapshot.pnl).select()).all()
------------------------------------------------------------
Traceback (most recent call last):
File "<ipython console>", line 1, in ?
File "<string>", line 1, in <lambda>
File
"/home/titan/johnh/dev/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc3dev_r5205-py2.4.egg/sqlalchemy/orm/query.py",
line 52, in generate
fn(self, *args[1:], **kw)
File
"/home/titan/johnh/dev/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc3dev_r5205-py2.4.egg/sqlalchemy/orm/query.py",
line 624, in filter
raise sa_exc.ArgumentError("filter() argument must be of type
sqlalchemy.sql.ClauseElement or string")
ArgumentError: filter() argument must be of type
sqlalchemy.sql.ClauseElement or string
Sorry to be flailing around a bit here -- hopefully you can shed some
light for me!
JDH
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
