I applied your top patch. I cannot test the second issue. If you can and want to send me a patch, I will be happy to include it. Thanks.
On Mar 3, 10:57 am, SergeyPo <[email protected]> wrote: > PATCH > > File gluon/sql.py, line #2987 in _select method of Set (SQLSet) class > should be: > > return '%s %s FROM (SELECT w_tmp.*, ROWNUM w_row FROM (SELECT %s FROM > %s%s%s) w_tmp WHERE ROWNUM<=%i) %s %s %s;' % (sql_s, sql_f, sql_f, > sql_t, sql_w, sql_o, lmax, sql_t, sql_w_row, sql_o) > > Previoisly was: > return '%s %s FROM (SELECT w_tmp.*, ROWNUM w_row FROM (SELECT %s FROM > %s%s%s) w_tmp WHERE ROWNUM<=%i) %s %s;' % (sql_s, sql_f, sql_f, sql_t, > sql_w, sql_o, lmax, sql_t, sql_w_row) > > This adds GROUP BY clause at the end of outer query and fixes error in > Oracle. This make possible queries with left join and grouping, e.g. > > db(cond).select(db.carts.ALL, db.carts.id.count(), > left=db.c_h.on(many_to_many), > groupby=db.carts.ALL, > orderby=carts_orderby, > limitby = session.carts_limitby) > > I tested this very extensively. > > Another problem I reported in this post: > > data = db(db.headers.id == db.alarms.header).select( > db.header.name, db.alarms.komment, > limitby=(0, 5) > ) > > To make it working in Oracle you should add all fields taking part in > WHERE clause to the list of fields to SELECT, i.e. > > data = db(db.headers.id == db.alarms.header).select( > db.header.name, db.alarms.komment, db.headers.id, db.alarms.header > limitby=(0, 5) > ) > > Since original version works in MySQL and SQLite, I think to make DAL > more DB-independent, this feature should be added to the _select > method. I could not (due to time restrictions) fix it myself in DAL, > but hopefully Massimo as author will make it easily. The resulting > query should include the fields in question in the most inner nested > request, i.e.: > > return '%s %s FROM (SELECT w_tmp.*, ROWNUM w_row FROM (SELECT %s > <<<conditions fields go here>>> FROM %s%s%s) w_tmp WHERE ROWNUM<=%i) > %s %s %s;' % (sql_s, sql_f, sql_f, sql_t, sql_w, sql_o, lmax, sql_t, > sql_w_row, sql_o) > > leaving outer select with only the fields requested by SQLSet. > > Hope this helps, > > Sergey > > On Mar 1, 5:22 pm, SergeyPo <[email protected]> wrote: > > > Limitby clause works when you select db.table.ALL > > > It must be possible to use aliases carefully to be able to select > > eequired fields only. > > > On 28 фев, 16:02, mdipierro <[email protected]> wrote: > > > > No limitby is not working on Oracle with Joins. This is because Oracle > > > does not support pagination. We do it anyway using a trick (three > > > nested select as suggested by the oracle docs) but the trick breaks > > > down with joins. > > > > On Feb 27, 11:50 am, SergeyPo <[email protected]> wrote: > > > > > If you remove limitby it works. I reported this error about 6 months > > > > ago, tried many workarounds and thought we fixed it. But somehow it > > > > appeared at my client's site. Versions 1.65 and current 1.75 > > > > downloaded this week. > > > > > On Feb 27, 8:20 pm, Thadeus Burgess <[email protected]> wrote: > > > > > > Just going out on a limb here. > > > > > > Have you tried the query where it selects all columns instead of just > > > > > name and komment? > > > > > > -Thadeus > > > > > > 2010/2/27 SergeyPo <[email protected]>: > > > > > > > SELECT headers1.name, alarms1.komment FROM (SELECT w_tmp.*, ROWNUM > > > > > > w_row FROM (SELECT headers1.name, alarms1.komment FROM headers1, > > > > > > alarms1 WHERE headers1.id=alarms1.header ORDER BY headers1.id, > > > > > > alarms1.id) w_tmp WHERE ROWNUM<=5) headers1, alarms1 WHERE > > > > > > headers1.id=alarms1.header AND w_row > 0; > > > > > > > Traceback (most recent call last): > > > > > > File "c:\web2pyNEW\gluon\restricted.py", line 173, in restricted > > > > > > exec ccode in environment > > > > > > File "c:/web2pyNEW/applications/test/controllers/default.py", line > > > > > > 11, > > > > > > in <module> > > > > > > File "c:\web2pyNEW\gluon\globals.py", line 96, in <lambda> > > > > > > self._caller = lambda f: f() > > > > > > File "c:/web2pyNEW/applications/test/controllers/default.py", line > > > > > > 7, > > > > > > in index > > > > > > data = > > > > > > db(db.headers1.id==db.alarms1.header).select(db.headers1.name, > > > > > > db.alarms1.komment, limitby=(0, 5)) > > > > > > File "c:\web2pyNEW\gluon\sql.py", line 3042, in select > > > > > > rows = response(query) > > > > > > File "c:\web2pyNEW\gluon\sql.py", line 3037, in response > > > > > > db._execute(query) > > > > > > File "c:\web2pyNEW\gluon\sql.py", line 970, in <lambda> > > > > > > oracle_fix_execute(a,self._cursor.execute) > > > > > > File "c:\web2pyNEW\gluon\sql.py", line 603, in oracle_fix_execute > > > > > > return execute(command[:-1], args) > > > > > > DatabaseError: ORA-00904: "HEADERS1"."ID": invalid identifier > > > > > > > On 26 фев, 21:22, Thadeus Burgess <[email protected]> wrote: > > > > > >> please print the output of > > > > > > >> db(db.headers.id == db.alarms.header)._select(db.header.name, > > > > > >> db.alarms.komment, limitby=(0,5)) > > > > > > >> -Thadeus > > > > > > >> On Fri, Feb 26, 2010 at 12:00 PM, SergeyPo <[email protected]> > > > > > >> wrote: > > > > > >> > Limitby clause still is not working with Oracle backend when > > > > > >> > used in > > > > > >> > queries that have any kind of join, left or inner. > > > > > > >> > db.define_table('headers', > > > > > >> > SQLField('name', 'string') > > > > > >> > ) > > > > > > >> > db.define_table('alarms', > > > > > >> > SQLField('header', db.headers), > > > > > >> > SQLField('komment', 'string') > > > > > >> > ) > > > > > > >> > data = db(db.headers.id == db.alarms.header).select( > > > > > >> > db.header.name, db.alarms.komment, limitby=(0, 5) > > > > > >> > ) > > > > > > >> > ...gives Oracle error "wrong identifier 'alarms'.'headers' > > > > > > >> > But in fact this is not matter of wrong fields, problem is with > > > > > >> > limitby construction. > > > > > > >> > Queries using left join also give error, but another one. In both > > > > > >> > cases error message is quite irrelevant to real problem. E.g. I > > > > > >> > need a > > > > > >> > pretty complex query with groupby, count and left join... Error > > > > > >> > message in this case will be 'this is not valid groupby clause'. > > > > > >> > Obviously, problem is in aliases that are used for nested queries > > > > > >> > required by Oracle to limit rows in result set. > > > > > > >> > Any help with DAL or workarounds please! I am ready to help with > > > > > >> > testing. > > > > > > >> > -- > > > > > >> > 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 > > > > > >> > athttp://groups.google.com/group/web2py?hl=en.-Скрыть цитируемый > > > > > >> > текст - > > > > > > >> - Показать цитируемый текст - > > > > > > > -- > > > > > > 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 > > > > > > athttp://groups.google.com/group/web2py?hl=en. -- 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.
