It should work with python 2.7 but not previous version. You can also do:
s=db.club.created_on.year() | db.club.created_on.month() |
db.club.created_on.day()
dates =
db(query).select(db.club.created_on.year(),db.club.created_on.month(),db.club.created_on.day()
,
count,orderby=~s, limitby=limitby, groupby=s)
On Wednesday, 17 October 2012 11:54:29 UTC-5, Paolo wrote:
>
> Hi Massimo, thanks for the suggested query but unfortunately I got this
> error:
>
> Traceback (most recent call last):
> File "/home/paolo/Dropbox/git/web2py/gluon/restricted.py", line 208, in
> restricted
> ccode = compile2(code,layer)
> File "/home/paolo/Dropbox/git/web2py/gluon/restricted.py", line 193, in
> compile2
> return compile(code.rstrip().replace('\r\n','\n')+'\n', layer, 'exec')
> File "/home/paolo/Dropbox/git/web2py/applications/bikend/models/clubDB.py"
> <http://127.0.0.1:8000/admin/default/edit/bikend/models/clubDB.py>, line 29
> dates = db(query).select(*s,count,orderby=~join(s), limitby=limitby,
> groupby=join(s))
> SyntaxError: only named arguments may follow *expression
>
> If that can help, I've tried without the wildcard but it failed, web2py
> was blocked and python took 100% of the cpu.
>
> Paolo
>
> On Wednesday, October 17, 2012 2:35:23 PM UTC+2, Massimo Di Pierro wrote:
>>
>>
>> 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
>>>>>
>>>>
--
