Hi all, I've problems on defining self join queries on postgres, the query,
works on sqlite, is defined as follows:
start = db.record.with_alias('start')
end = db.record.with_alias('end')
query = ((start.station_id == 11) &
(end.station_id == 12))
rows = db( query ).select(start.gathered_on,start.mac, end.gathered_on, end.
mac,
orderby=start.gathered_on.epoch(),
left= start.on( (start.mac == end.mac) ))
The table is:
db.define_table('record',
Field('station_id', 'reference station'),
Field('log_id', 'reference log'),
Field('mac'),
Field('gathered_on', 'datetime'),
)
The generated query is:
SELECT start.gathered_on, start.mac, end.gathered_on, end.mac
FROM record AS end
LEFT JOIN record AS start ON (start.mac = end.mac) WHERE ((start.station_id
= 11) AND (end.station_id = 12))
ORDER BY EXTRACT(epoch FROM start.gathered_on);
and it fails raising the following error:
Traceback (most recent call last):
File "/home/paolo/Dropbox/git/web2py/gluon/restricted.py", line 212, in
restricted
exec ccode in environment
File
"/home/paolo/Dropbox/git/web2py/applications/vtraffic/controllers/default.py"
<http://127.0.0.1:8000/admin/default/edit/vtraffic/controllers/default.py>,
line 655, in <module>
File "/home/paolo/Dropbox/git/web2py/gluon/globals.py", line 193, in <lambda>
self._caller = lambda f: f()
File
"/home/paolo/Dropbox/git/web2py/applications/vtraffic/controllers/default.py"
<http://127.0.0.1:8000/admin/default/edit/vtraffic/controllers/default.py>,
line 37, in index
left= start.on( (start.mac == end.mac) ))
File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 8966, in select
return adapter.select(self.query,fields,attributes)
File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1636, in select
return self._select_aux(sql,fields,attributes)
File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1601, in _select_aux
self.execute(sql)
File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1714, in execute
return self.log_execute(*a, **b)
File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1708, in log_execute
ret = self.cursor.execute(*a, **b)
ProgrammingError: syntax error at or near "end"
LINE 1: SELECT start.gathered_on, start.mac, end.gathered_on, end.m...
What should I have to do?
Regards,
Paolo
--
