If you try to limitby complex query in oracle you get an error
(duplicate records). Gluon currently works well for queries like
db().select(db.details.ca_date, orderby=~db.details.ca_date, limitby=
(0,1))
but does not work for joint tables e.g.:
db(db.headers.id==db.details.header) \
.select(limitby=limitby, orderby=orderby, *fields)
Error happens because DAL adds tables list at end of outer query, and
no join condition for these tables. Therefore rows become duplicated
by *limitby* limit value. Here is a patch for gluon/sql.py that solved
the problem for me. It just adds WHERE clause used in the inner query,
to the outer query. Don't know if it is efficient but at least works.
starting with line 1856:
if attributes.get('limitby', False):
# oracle does not support limitby
(lmin, lmax) = attributes['limitby']
if self._db._dbname == 'oracle':
if not attributes.get('orderby', None):
sql_o += ' ORDER BY %s' % ', '.join([t + '.id'
for t in tablenames])
if len(sql_w) > 1: sql_w_row = sql_w + ' AND w_row >'
else: sql_w_row = 'WHERE w_row >'
return '%s %s FROM (SELECT w_tmp.*, ROWNUM w_row FROM
(SELECT %s FROM %s%s%s) w_tmp WHERE ROWNUM<=%i) %s %s %i;' \
% (sql_s, sql_f, sql_f, sql_t, sql_w,
sql_o, lmax, sql_t, sql_w_row, lmin)
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"web2py Web Framework" group.
To post to this group, send email to web2py@googlegroups.com
To unsubscribe from this group, send email to
web2py+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/web2py?hl=en
-~----------~----~----~----~------~----~------~--~---
