If you are doing a left join: db(db.person.id >0).select(db.person.ALL,db.thing.ALL,left=db.thing.on(db.thing.owner_id== db.person.id))
if you are doing an inner join: db(db.person.id >0).select(db.person.ALL,db.thing.ALL,join=(db.thing.owner_id==db.person.id )) which is equivalent to db((db.person.id>0)&(db.thing.owner_id==db.person.id )).select(db.person.ALL,db.thing.ALL) For inner joins there is no db.thing.on because there is a symmetry because the tables being joined. On Friday, 6 June 2014 04:05:30 UTC-5, Armin Würtenberger wrote: > > Hi, I'm using web2py quite a while now, and I am still pleased with it's > functionality. > Recently I started to use record versioning and found, that joins with > versioned tables do not work anymore: > > Here's the example (using the app welcome): > #-------------------------------------------------------------- > MODEL: > [...] > > db._common_fields.append(auth.signature) > > db.define_table('person', > Field('name'), > format='%(name)s') > > db.define_table('thing', > Field('name'), > Field('owner_id', 'reference person'), > format='%(name)s') > > ## after defining tables, uncomment below to enable auditing > auth.enable_record_versioning(db) > #-------------------------------------------------------------- > #-------------------------------------------------------------- > COMMAND LINE: > saturn:$ ./web2py.py -S welcome -M > web2py Web Framework > Created by Massimo Di Pierro, Copyright 2007-2014 > Version 2.9.5-stable+timestamp.2014.03.16.02.35.39 > Database drivers available: SQLite(sqlite3), MySQL(pymysql), > PostgreSQL(pg8000), MSSQL(pyodbc), DB2(pyodbc), Teradata(pyodbc), > Ingres(pyodbc), IMAP(imaplib) > 2014-06-06 10:44:05,596 - WARNING - web2py - import IPython error; use > default python shell > Python 2.7.3 (default, Nov 17 2012, 19:54:34) > [GCC 4.2.1 Compatible Apple Clang 4.1 ((tags/Apple/clang-421.11.66))] on > darwin > Type "help", "copyright", "credits" or "license" for more information. > (InteractiveConsole) > >>> db.tables > ['auth_user', 'auth_group', 'auth_membership', 'auth_permission', > 'auth_event', 'auth_cas', 'person', 'thing', 'person_archive', > 'thing_archive'] > >>> db(db.person.id > >0).select(db.person.ALL,db.thing.ALL,join=db.thing.on(db.thing.owner_id== > db.person.id)) > Traceback (most recent call last): > File "<console>", line 1, in <module> > File "/Users/mtpgmbh/Documents/datenbank/web2py_2.9.5/gluon/dal.py", > line 10525, in select > return adapter.select(self.query,fields,attributes) > File "/Users/mtpgmbh/Documents/datenbank/web2py_2.9.5/gluon/dal.py", > line 2458, in select > return super(SQLiteAdapter, self).select(query, fields, attributes) > File "/Users/mtpgmbh/Documents/datenbank/web2py_2.9.5/gluon/dal.py", > line 1869, in select > sql = self._select(query, fields, attributes) > File "/Users/mtpgmbh/Documents/datenbank/web2py_2.9.5/gluon/dal.py", > line 1773, in _select > query = self.common_filter(query,tablenames_for_common_filters) > File "/Users/mtpgmbh/Documents/datenbank/web2py_2.9.5/gluon/dal.py", > line 2330, in common_filter > query = query & table._common_filter(query) > File "/Users/mtpgmbh/Documents/datenbank/web2py_2.9.5/gluon/dal.py", > line 8826, in <lambda> > if tn == name or getattr(db[tn],'_ot',None)==name]) > TypeError: reduce() of empty sequence with no initial value > >>> # workaround: > >>> db((db.person.id>0)&(db.thing.owner_id==db.person.id > )).select(db.person.ALL,db.thing.ALL) > <Rows (0)> > >>> > #-------------------------------------------------------------- > > As one can see in the last line, there is a workaround, but I think it's > not that clear as it could/should be. > -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.