Hi Massimo,
thanks for the suggestions, at this point I have to understand what to do:
- use your last proposal but in this case the date is no longer a data
object but three different columns, and this will lead to a more difficult
logic
- put a date field in the table definition, this may simplify all the
things, isn't it?
I will check it later this week-end.
Thanks
Paolo
On Wednesday, October 17, 2012 7:25:59 PM UTC+2, Massimo Di Pierro wrote:
>
> 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
>>>>>>
>>>>>
--
