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
>>>>
>>>
--
