web2py generate SQL that PostgreSQL doesn't accept. For instance,
SELECT DISTINCT a.id, a.uid, a.name FROM a, b WHERE ((((a.deleted <> 'T') AND (a.id > 0)) AND (((b.deleted <> 'T') AND (b.id > 0)) AND (b.id = 1))) AND (b.uid = a.uid)) ORDER BY b.id, a.id LIMIT 1 OFFSET 0; It fails because select list is not contain "b.id". I change gluon/dal.py(Version 1.94.5) as follows. It is work well. But I am not sure that it is a good practice. === modified file 'gluon/dal.py' Version 1.94.5 @@ -1039,2 +1039,6 @@ -------------------------------------------------------------- if not orderby and tablenames: - sql_o += ['%s.%s'%(t,x) for t in tablenames for x in ((hasattr(self.db[t],'_primarykey') and self.db[t]._primarykey) or [self.db[t]._id.name])] + orderby_fields = ['%s.%s'%(t,x) for t in tablenames for x in ((hasattr(self.db[t],'_primarykey') and self.db[t]._primarykey) or [self.db[t]._id.name])] + sql_o += ' ORDER BY %s' % ', '.join(orderby_fields) + for orderby_field in orderby_fields: + if orderby_field not in self._colnames: + sql_f += ', %s' % orderby_field Your opinion will be much appreciated, Cheers,
