The generic formula for a 3 way join is:
db.define_table('a',db.Field('name'))
db.define_table('b',db.Field('name'))
db.define_table('t',db.Field('name'),db.Field('aid',db.a),db.Field('bid',db.b))
rows=db().select(db.t.name,db.a.name,db.b.name,
left=[db.a.on(db.a.id==db.t.aid),db.b.on(db.b.id==db.t.bid)])
which in your case would be:
rows=db().select(db.avariasdb.ALL, db.edificios.nome,
db.tipoavaria.nome,
left=[db.edificios.on(db.edificios.id==db.avariasdb.edificio,
db.tipoavaria.on(db.tipoavaria.id==db.avariasdb.tipo] )
On Jan 28, 5:33 am, Oinquer <[email protected]> wrote:
> Bear with me my language is not english, so ill try to be plain and
> straight
>
> i have a table that references another 2 tables.
> when im generating the form works nicely as i want, but when i
> retrieve the list with a db select all i dont get the referenced names
> of the tables...i get only the id's...how should i do it to get the
> names ('nome')? thanks.
> im thinking about a join, but cant seem to do it....
>
> db.define_table('edificios',
> Field('nome'),
> Field('morada'))
>
> db.define_table('tipoavaria',
> Field('nome'))
>
> db.define_table('avariasdb',
> Field('user', db.auth_user, default=auth.user_id,
> writable=False,readable=False),
> Field('edificio', db.edificios),
> Field('piso'),
> Field('tipo', db.tipoavaria),
> Field('descricao', 'string', widget=SQLFORM.widgets.text.widget),
> Field('data', 'datetime', default=request.now, writable=False))
>
> db.avariasdb.edificio.requires = IS_IN_DB(db, 'edificios.id',
> 'edificios.nome', zero=T('Choose One'))
> db.avariasdb.tipo.requires = IS_IN_DB(db, 'tipoavaria.id',
> 'tipoavaria.nome', zero=T('Choose One'))
