DefaultScope implementation?

Sorry for the provacative title. But I’m seriously scratching my head.

I have a SaaS app (multi-tenant). I’ve decided to add a clientID field to each table to facilitate limiting records to a particular site.

I thought that defaultScope would work well for this purpose. Here is my code:


	public function defaultScope()

	/* defaultScope is used to enforce clientID (site) context. Will only retrieve data with that user's site ID.

	*  User clientID (site) is set during login process in /components/UserIdentity class

	*/

    { 

		if(!Yii::App()->user->isGuest) {

		return array('condition'=>"clientId='".Yii::App()->User->clientId."'");

		} else { 

			return array();

			}

    }

The problem I am having is that when I have a dataProvider that uses a ‘with’ and joins 2 tables I get an ambigious column error, since the clientID field is present in multiple tables. OK, so I change the code to say:


return array('condition'=>"t.clientId='".Yii::App()->User->clientId."'");

OK, this works except that I have a few dropDownList() methods that return all records. Yii seems to only use the ‘t’ alias when using multiple tables - when selecting data to populate a dropdown list Yii does not seem to use the ‘t’ alias. This results in a DB Exception stating there is no column named t.clientID.

All I want is to have ONE defaultScope() that handles both single and multi table scenarios. I know I can just override it but this seems silly. What am I missing??

I had similar problems using defaultScope and composite-key validation extension in multi-tenant SaaS system.

I foresaw more trouble, because I need to extend defaultScope even further and soon have to add more named scopes for a complicated RBAC system.

I got so frustrated that I ended up disambiguating the tables and fields in the database by adding a unique prefix to each table and its fields.

Some people may frown upon this decision (because it is going to slow down the program by 0.000001 milliseconds), but the benefit (never ambigious again!) far outweigh this drawback. And it also makes coding much easier when using controllers and views with multiple models, because you can immediately identify the model to which each field belongs, without having to go back to the controller to see which table is represented by $model.

Just my thoughts.