s=db.club.created_on.year(),db.club.created_on.month(),db.club.created_on.day() def join(s): return reduce(lambda a,b:a|b,s) dates = db(query).select(*s,count,orderby=~join(s), limitby=limitby, groupby=join(s))
On Wednesday, 17 October 2012 01:31:55 UTC-5, Paolo wrote: > > Hi Cliff, > I got the reasons of postgres, but I don't know how to fix it. The query > is actually very simple, I have several post, I want to group them by s, > and get the number of post for each s. Where s is: > s=db.club.created_on.year() | db.club.created_on.month() | > db.club.created_on.day() > In the select I may created_on and use s instead, something like that: > dates = db(query).select(s,count,orderby=~s, limitby=limitby, groupby=s) > > but doing that I got this error: > 2012-10-17 08:27:59,210 - web2py - ERROR - Traceback (most recent call > last): > File "/home/paolo/Dropbox/git/web2py/gluon/restricted.py", line 209, in > restricted > exec ccode in environment > File > "/home/paolo/Dropbox/git/web2py/applications/bikend/controllers/club.py", > line 140, in <module> > File "/home/paolo/Dropbox/git/web2py/gluon/globals.py", line 184, in > <lambda> > self._caller = lambda f: f() > File > "/home/paolo/Dropbox/git/web2py/applications/bikend/controllers/club.py", > line 3, in index > d= dict(clubs = get_clubs()) > File > "/home/paolo/Dropbox/git/web2py/applications/bikend/models/clubDB.py", line > 25, in get_clubs > dates = db(query).select(s,count,orderby=~s, limitby=limitby, > groupby=s) > File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 8787, in select > return adapter.select(self.query,fields,attributes) > File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 2127, in select > return super(SQLiteAdapter, self).select(query, fields, attributes) > File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1615, in select > return self._select_aux(sql,fields,attributes) > File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1596, in > _select_aux > return processor(rows,fields,self._colnames,cacheable=cacheable) > File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1974, in parse > fields[j].type,blob_decode) > IndexError: list index out of range > > Paolo > > On Wednesday, October 17, 2012 4:45:35 AM UTC+2, Cliff Kachinske wrote: >> >> I don't know how it possibly worked in sqlite, but this is an aggregate >> query combined with a non-aggregate query. >> >> In other words, the count is a property of an aggregation of rows in the >> database, whereas created_on is a property of individual rows. >> >> This confuses Postgres. It doesn't know if you want the aggregate result >> (count) or the result for individual rows (created_on). It cannot deliver >> both from the same query. >> >> What are you trying to find out in your query? >> >> >> >> On Tuesday, October 16, 2012 4:59:36 PM UTC-4, Paolo wrote: >>> >>> Dear all, >>> I've just switched from sqlite to postgres, and now I have problems with >>> few queries. >>> One query that works correctly on sqlite and fails on postgres is the >>> following: >>> s=db.club.created_on.year() | db.club.created_on.month() | >>> db.club.created_on.day() >>> count = db.club.id.count() >>> dates = >>> db(query).select(db.club.created_on,count,orderby=~db.club.created_on, >>> limitby=limitby, groupby=s) >>> >>> Now on postgres, it raises the following error: >>> ProgrammingError: column "club.created_on" must appear in the GROUP BY >>> clause or be used in an aggregate function >>> LINE 1: SELECT club.created_on, COUNT(club.id) FROM club WHERE (((c... >>> >>> I read online that the fields in the select must be on the groupby as >>> well. The problem is that by grouping even by club.created_on (by adding >>> groupby=s | club.created_on) the result is totally different. What can I >>> do to tackle this problem? >>> >>> Best, >>> Paolo >>> >> --
