Hi Niphlod, yes it solved the problem.
Now I have an other problem, I need to make a self left join, but limited
to 1 match only. Namely, I've just need to make the join only for the first
matching row. I've tried something like:
rows = db( query ).select(start.gathered_on,start.mac,start.id,
end.gathered_on, end.mac, end.id,
start.gathered_on.epoch(),
end.gathered_on.epoch(),
orderby=start.gathered_on.epoch(),
left= start.on(start.mac == end.mac),
groupby=start.id,
cacheable = True)
This actually works on sqlite but on postgres failed because on postgres
you can group only onto the field expressed in the select. Moreover, even
if on sqlite it works, It select the wrong rows.
Online I have found this post:
http://archives.postgresql.org/pgsql-novice/2011-01/msg00069.php
and now I am trying to understand how to define a sub_select with the
limitby=1 to carry out the left join.
Any idea ?
Regards,
Paolo
On Monday, January 7, 2013 2:21:33 PM UTC+1, Niphlod wrote:
>
> did you try changing "start" and "end" as aliases ?
> while "start" on postgresql isn't reserved ( but is a reserved keyword for
> t-sql:2003 and t-sql:1999), "end" is reserved even for postgresql.
>
> On Monday, January 7, 2013 1:21:27 PM UTC+1, Paolo valleri wrote:
>>
>> 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
>>
>
--
