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.
