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 --