Rendering the foreign tables of foreign tables as strings in a pulldown in web2py appadmin

Question

Following up on rendering foreign keys as a pulldown in web2py appadmin, I would like to be able to render references of references as strings in pulldowns.

In other words, the previous question dealt with how to render the foreign keys of a table as a pulldown. This question deals with the foreign keys of the foreign keys.

I am now able to render the foreign keys of the table wallet_asset:

db.define_table('wallet_asset',
    Field('wallet_id', 'reference wallet'),
    Field('asset_id', 'reference asset'),
)

by defining the referenced tables like so:

db.define_table('asset',
    Field('name', unique=True, requires=IS_NOT_EMPTY()),
    Field('description', 'text'),
                format='%(name)s'
)

db.define_table('wallet',
    Field('name', unique=True, requires=IS_NOT_EMPTY()),
    Field('description', type='text'),
    format='%(name)s'
)

but now I want to have a table user_wallet_asset :

db.define_table('user_wallet_asset',
    Field('user_id', 'reference auth_user'),
    Field('wallet_asset_id', 'reference wallet_asset'),
    Field('address')
)

And when I enter records using AppAdmin, I want the field wallet_asset_id to render in a pulldown as a string based on the representation of foreign keys in the tables wallet andasset`, something like:

asset.name is held in wallet.name

The reason this is not straightforward is that user_wallet_asset references wallet_asset which then references wallet and asset.

Presumably the final solution looks something like this:

db.define_table('wallet_asset',
    Field('wallet_id', 'reference wallet'),
    Field('asset_id', 'reference asset'),
    format='%(asset_id).name is held in %(wallet_id).name'
)

Answer 1

The format argument can take a function that generates the values you want:

db.define_table('wallet_asset',
    Field('wallet_id', 'reference wallet'),
    Field('asset_id', 'reference asset'),
    format=lambda r: '%s is held in %s' % (r.asset_id.name, r.wallet_id.name))

Note, r.asset_id.name and r.wallet_id.name involve recursive selects, so when a dropdown is generated for the db.user_wallet_asset.wallet_asset_id field, there will be two database selects for each item in the dropdown.

For further details, you may find this answer helpful.