Fred C wrote:
On May 9, 2006, at 6:21 AM, Robert Szefler wrote:
Oleg Broytmann wrote:
On Mon, May 08, 2006 at 01:03:53PM -0400, Fred C wrote:
I would like to execute a request with a GROUP BY and I understand
that can be done directly with SQLObject and has to be done using
SQLBuilder. But how ?
connection.query(connection.sqrepr(Select("table", ["SUM(price)"],
groupBy="shop")))
Ouch - second parameter is expected to be the where clause, right?
My sepcific problem is:
SELECT COUNT(*) FROM table WHERE MONTH(dateAdded)=5 GROUP BY DAY
(dateAdded)
Now, how do you (wrestle to) express that in SQLObject?
I did that and that works fine.
conn = searchTags._connection
sel = conn.sqlrepr(Select((func.COUNT('*'), searchTags.q.tag),
groupBy=searchTags.q.tag,
orderBy=DESC(func.COUNT('*')),
limit='100'))
tags = list(conn.queryAll(sel))
-fred-
OK, already found the solution (the query is a bit more complicated):
conn.sqlrepr(Select(
[func.DAY(Wydarzenia.q.data),func.COUNT(Wydarzenia.q.id)],
where=(AND(
func.MONTH(Wydarzenia.q.data)==miesiac,
func.YEAR(Wydarzenia.q.data)==rok
)),
groupBy=func.DAY(Wydarzenia.q.data)
))
I've had to change COUNT(*) to COUNT(id), though. func.COUNT('*') does
not work in general as it generates COUNT('*') in SQL. (Well, actually
it works by a leap of luck here, but relying on a hack is bad).
Any clue on how to get COUNT(*) itself?
-------------------------------------------------------
Using Tomcat but need to do more? Need to support web services, security?
Get stuff done quickly with pre-integrated technology to make your job easier
Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
_______________________________________________
sqlobject-discuss mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
