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 [email protected].
For more options, visit https://groups.google.com/d/optout.
