Hi all:
I’m working on a multi-tenant application that uses MS SQL Server schemas to separate tenant data. Both my tenant and user tables use surrogate primary keys [int identity(1,1)] but I want to relate the user to the tenant through a different column (User->TenantDbUser => Tenant->DbUser) that contains the tenant’s db user name and not the primary key. The reason is that when an app request starts I can switch the db connection to that belonging to the user’s schema. The tenant and user tables are the only shared tables in the database (dbo); each tenant’s schema will be encapsulated so there’s no need to store tenant references in the schema’s tables.
User model:
public function relations()
{
return array(
'tenant' => array(self::BELONGS_TO, 'Tenant', 'TenantDbUser'),
);
}
Everything works fine at the database level, but it seems to me that in the relations() method the BELONGS_TO relationship automatically looks for the parent’s primary key, which won’t work in this case.
Is there any way to work around this with stock Yii code or do I have to extend the CActiveRecord class to allow this functionality? Any ideas are welcome.
*** Update ***
Changing the relationship to:
'tenant' => array(self::BELONGS_TO, 'Tenant', 'SomeOtherIntegerColumn','on'=>'1=1 OR [tenant].[DbUser] = [t].[TenantDbUser]'),
solves the SQL Server error (casting an nvarchar to an integer) but doesn’t load the parent data. I reckon it’s because the foreign key in the table is still nvarchar. I’d really like NOT to add an integer column to use as foreign key.
Thanks,