Hi,
I have been using SQLFORM.grid to display some legacy Oracle tables. While
it works fine when the query is for a single table, as soon as the query
involves a join between two tables the grid returns multiple duplicate
entries. (I'm using web2py 2.13.4-)
The result looks like this:
Pointnumber Pointname Referencename
1 RTU 1 Status & Control Harris Test
1 RTU 1 Status & Control Harris Test
1 RTU 1 Status & Control Harris Test
1 RTU 1 Status & Control Harris Test
1 RTU 1 Status & Control Harris Test
2 RTU 2 Status & Control Harris Test
2 RTU 2 Status & Control Harris Test
2 RTU 2 Status & Control Harris Test
2 RTU 2 Status & Control Harris Test
2 RTU 2 Status & Control Harris Test
3 RTU 3 Status & Control Harris Test
3 RTU 3 Status & Control Harris Test
3 RTU 3 Status & Control Harris Test
3 RTU 3 Status & Control Harris Test
3 RTU 3 Status & Control Harris Test
...
The following is the controller that generated the previous results.
..................................................
def search1():
fields =
[db2.STATUSPOINT.POINTNUMBER,db2.STATUSPOINT.POINTNAME,db2.AOR.REFERENCENAME]
maxtextlengths = {
'STATUSPOINT.POINTNAME': 30,
'AOR.REFERENCENAME': 30,
}
query = ((db2.STATUSPOINT.POINTNUMBER < 100)&\
(db2.STATUSPOINT.POINTACCESSAREA == db2.AOR.AOR))
orderby = [db2.STATUSPOINT.POINTNUMBER]
grid=SQLFORM.grid(
query=query,
deletable=False,editable=False,details=False,
searchable=True,fields=fields,
paginate=5,csv=False,maxtextlengths=maxtextlengths,
orderby=orderby,
)
print "search1 grid db2._timings %s" % db2._timings
print type(grid)
print len(grid)
return dict(grid=grid)
..................................................
- the number of duplicates(5) is the same as the value for paginate.
- paging forward shows the same values.
- the sql reported by db2._timings is
[("ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'",
0.00066494941711425781), ("ALTER SESSION SET
NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS'", 0.0005130767822265625),
('SELECT count(*) FROM AOR,STATUSPOINT WHERE
((STATUSPOINT.POINTNUMBER < 100) AND (STATUSPOINT.POINTACCESSAREA =
AOR.AOR))', 0.0013380050659179688), ('SELECT
STATUSPOINT.POINTNUMBER, STATUSPOINT.POINTNAME, AOR.REFERENCENAME, AOR.AOR
FROM (SELECT w_tmp.*, ROWNUM w_row FROM
(SELECT STATUSPOINT.POINTNUMBER, STATUSPOINT.POINTNAME, AOR.REFERENCENAME,
AOR.AOR FROM AOR, STATUSPOINT WHERE
((STATUSPOINT.POINTNUMBER < 100) AND (STATUSPOINT.POINTACCESSAREA =
AOR.AOR)) ORDER BY STATUSPOINT.POINTNUMBER) w_tmp
WHERE ROWNUM<=25) AOR, STATUSPOINT WHERE ((STATUSPOINT.POINTNUMBER < 100)
AND (STATUSPOINT.POINTACCESSAREA =
AOR.AOR)) AND w_row > 20 ORDER BY STATUSPOINT.POINTNUMBER',
0.014330863952636719)]
- when I execute this sql from the command line using sqlplus, I see the
same results with all the duplicates
- I believe there is flaw with the sql statements generated by web2py
- I wrote some sql that return more appropriate results but I'm not
familiar enough with the inner workings of web2py to be able to implement
it.
- If anyone has suggestions about where to start, what modules to change
and best coding practices, I'd appreciate the pointers.
SELECT c1 "STATUSPOINT.POINTNUMBER", c2 "STATUSPOINT.POINTNAME", c3
"AOR.REFERENCENAME"
FROM (
SELECT w_tmp.c1, w_tmp.c2, w_tmp.c3, ROWNUM rn
FROM (
SELECT STATUSPOINT.POINTNUMBER c1, STATUSPOINT.POINTNAME c2,
AOR.REFERENCENAME c3
FROM AOR, STATUSPOINT
WHERE STATUSPOINT.POINTACCESSAREA = AOR.AOR
ORDER BY STATUSPOINT.POINTNUMBER
) w_tmp
WHERE ROWNUM <= 30
)
WHERE rn > 20;
- Tom
--
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.
