Does this work?
many_to_many = (db.c_h.cart_id==db.carts.id)
rows=db(db.carts.id>0).select(db.carts.ALL, db.c_h.cart_id.count(),
left=db.c_h.on(many_to_many),
orderby=carts_orderby)
if not, what is in carts_orderby?
db.carts.ALL is like "*" in SQL cannot be in a orderby
limitby does not work with JOIN in Oracle. This is a known bug. The
problem is that Oracle does not allow pagination. web2py mimics it
using 3 nested selects. That does not seem to work with JOINS. Perhaps
you can help us debug it.
You can do pagination on the web2py side:
rows.response=rows.response[0:100]
On Oct 12, 9:25 am, SergeyPo <[email protected]> wrote:
> Without this clause the query does not work at all. In shown case it
> works in mysql and sqlite.
>
> On Oct 12, 6:11 pm, mdipierro <[email protected]> wrote:
>
> > I think there is something wrong with the query:
>
> > groupby=db.carts.ALL
>
> > is not allowed.
>
> > Massimo
>
> > On Oct 12, 8:45 am, SergeyPo <[email protected]> wrote:
>
> > > The following statement gives strange error with Oracle. This has
> > > something to do with oracle_fix in gluon/sql.py
>
> > > many_to_many = (db.c_h.cart_id==db.carts.id)
> > > db(db.carts.id>0).select(db.carts.ALL, db.c_h.cart_id.count(),
> > > left=db.c_h.on(many_to_many),
> > > groupby=db.carts.ALL,
> > > orderby=carts_orderby,
> > > limitby = session.carts_limitby)
>
> > > Traceback (most recent call last):
> > > File "C:\web2py\gluon\restricted.py", line 176, in restricted
> > > exec ccode in environment
> > > File "C:\web2py\applications\advantage/controllers/cart.py", line
> > > 164, in <module>
> > > File "C:\web2py\gluon\globals.py", line 103, in <lambda>
> > > self._caller = lambda f: f()
> > > File "C:\web2py\applications\advantage/controllers/cart.py", line
> > > 13, in new_cart
> > > carts, search_form, pages = prepare_carts_table('filter_by_cart')
> > > File "C:\web2py\applications\advantage/controllers/cart.py", line
> > > 158, in prepare_carts_table
> > > limitby = session.carts_limitby),
> > > File "C:\web2py\gluon\sql.py", line 2146, in select
> > > r = response(query)
> > > File "C:\web2py\gluon\sql.py", line 2141, in response
> > > self._db._execute(query)
> > > File "C:\web2py\gluon\sql.py", line 777, in <lambda>
> > > oracle_fix_execute(a,self._cursor.execute)
> > > File "C:\web2py\gluon\sql.py", line 471, in oracle_fix_execute
> > > return execute(command[:-1], args)
> > > DatabaseError: ORA-00979: not a GROUP BY expression
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/web2py?hl=en
-~----------~----~----~----~------~----~------~--~---
