Complex Relational Queries (Many-to-Many with multiple middle tables)

I have an interesting problem, and I can’t seem to fix it using the framework, which limits me in using some of the time-saving features of the framework.

I have four tables that are something along the following.














I want to link the users and companies together through AuthAssignments. Every AuthAssignment row has one and only one AuthItem, and therefore the company_id is implicitly there.

It would be nice if the aliasing of table names in many_many relationships had some consistency so that one could define company_id…

I tried the “with” assocation, and then tried defining the intermediate key for companies as “AuthItem.company_id”, but that predictably didn’t work…

And the system automatically preprends the join table’s alias to the beginning of that string, which means there’s no way to make it associate with another table.

Is there a way to do this? Or prevent Yii from automatically prepending the join table? Or possibly with select, such as "(SELECT AuthItem.company_id AS AuthAssignment.company_id)"?

I’ve been wrestling with this for a while, since it seems like there would be a lot of uses for such flexibility. I’m not an SQL expert, but it seems like there should be some sort of way to structure things such that there’s a consistently accessible syntax to do these complex operations with multiple tables. Ideally, the right solution should scale indefinitely while only making one query. I guess that seems like a lot to ask for, huh? I suppose I’m just generally so pleased with Yii it’s surprising when it doesn’t just work the way I would expect during development. :)

'roles'=>array(self::HAS_MANY, 'AuthAssignmentModel', 'userid','with'=>'authItem','condition'=>'authItem.type=2','index'=>'c_id','select'=>array('*','(SELECT company_id FROM `fly_AuthItem` B WHERE roles.itemname = as c_id')),

the ‘select’ property doesn’t throw an error, and when tryign the sql manually, it seems to not be a problem. However, I can’t index on it, which is kind of a problem, nor could I use it in a many_many relationship.

I suppose that’s possibly another issue, but it would be nice to be able to index based on an aliased field…

Anybody got any insights on this?