Thank you for your work. Can you submit a github pull request? If not we can do it but you may want to get the credit. Let us know.
On Saturday, 10 September 2016 20:54:18 UTC-5, tomt wrote: > > Hello, > > I have patched the select_limitby routine in oracle.py to allow for > successful pagination when joins are used. > > This is an example of the sql it generates: > > SELECT c0 "STATUSPOINT.POINTNUMBER", c1 "STATUSPOINT.POINTNAME", c2 > "AOR.REFERENCENAME", c3 "AOR.AOR" > FROM ( > SELECT w_tmp.c0, w_tmp.c1, w_tmp.c2, w_tmp.c3, ROWNUM rn > FROM ( > SELECT STATUSPOINT.POINTNUMBER c0, STATUSPOINT.POINTNAME c1, > AOR.REFERENCENAME c2, AOR.AOR c3 > FROM AOR, STATUSPOINT > WHERE (STATUSPOINT.POINTACCESSAREA = AOR.AOR) > ORDER BY STATUSPOINT.POINTNUMBER > ) w_tmp > WHERE ROWNUM <= 20 > )WHERE rn > 10 > > The sql I used is based on a suggestion from > https://blog.jooq.org/2014/06/09/stop-trying-to-emulate-sql-offset-pagination-with-your-in-house-db-framework/ > > It works successfully in my initial tests, but I realize that it's > possible that this change may cause some problems that I haven't tested for. > > I'm hopeful that this change may be considered for implementation into the > official web2py code. Please let me know if there is anything that I can > do to assist in this process. > > > ... gluon/packages/dal/pydal/adapters/oracle.py ... > > def select_limitby(self, sql_s, sql_f, sql_t, sql_w, sql_o, limitby): > if limitby: > (lmin, lmax) = limitby > if len(sql_w) > 1: > sql_w_row = sql_w + ' AND w_row > %i' % lmin > else: > sql_w_row = 'WHERE w_row > %i' % lmin > > # start of my code changes > > # remove blanks from sql_f > mysql_f = sql_f.replace(" ","") > # split into lists > myfields = mysql_f.split(",") > select1 = "SELECT" > select2 = "SELECT" > select3 = "SELECT" > for i in range(len(myfields)): > select1 += ' c%s "%s",' % (i,myfields[i]) > select2 += ' w_tmp.c%s,' % (i) > select3 += ' %s c%s,' % (myfields[i],i) > # remove trailing ',' > select1 = select1.rstrip(",") > select3 = select3.rstrip(",") > mysql = "%s\nFROM (\n %s ROWNUM rn\n FROM (\n %s" % > (select1,select2,select3) > mysql += "\n FROM %s\n %s\n %s" % (sql_t,sql_w,sql_o) > mysql += "\n ) w_tmp\n WHERE ROWNUM <= %s\n)WHERE rn > %s\n" % > (limitby[1],limitby[0]) > return mysql > #return 'SELECT %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) > > # end of my code changes > > return 'SELECT %s %s FROM %s%s%s;' % (sql_s, sql_f, sql_t, sql_w, > sql_o) > > ................................................. > > > -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
