Wednesday, November 9, 2011

web2py - add a foreign key to aut_user

I'm still doing my project in web2py, and I wanted to add a foreign key in my auth_user table.

Problem: in the file models/, you first declare the tables auth_* and then the other tables. So if you add your foreign key you'll get an error because the "foreign table" doesn't exist yet.
Example :

auth = Auth(db)
auth.settings.extra_fields['auth_user'] = [Field('myFK', db.foreignTable)]

db.define_table('foreignTable',Field('name', length=45) )

So I tried to define my foreignTable before settings.extra_fields, but it didn't work either...

Solution :
The only solution I could find was the following :
auth = Auth(db)
auth.settings.extra_fields['auth_user'] = [Field('myFK', integer)]

db.define_table('foreignTable', Field('name', length=45) )

db.auth_user.myFK.requires = IS_IN_DB(db, '', '%(name)s', zero=T('choose one')) 
At first I declare my column as an integer, and after I define my foreignTable. Then I link myFK to my foreignTable.

And that's it!