If I understand correctly the problem it's a matter of using windowing functions, that are not available on all db engines and far too complicated to expose a consistent api by the DAL. Picking out "the first match only" doesn't quite explain what you need in detail (maybe it can be worked out without windowing functions in a particular case).... can you please make an example of "real data" to start with and what you want the resultset to return ?
On Monday, January 7, 2013 7:46:17 PM UTC+1, Paolo valleri wrote: > > 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 >>> >> --
