I have multiple ActiveRecord models that specify relations to a central lookup table in order to provide code value lookup. In the simple case of a single ‘state’ field, these are of the form:
ModelA Relations:
'stateLkp' => array(self::BELONGS_TO, 'Lookup', '', 'alias'=>'modelaStateLkp',
'on'=>"modelaStateLkp.code=t.state and modelaStateLkp.category='MachineState'")
'submodels' => array(self::HAS_MANY, 'ModelB', 'ref_id')
ModelB Relations:
'stateLkp' => array(self::BELONGS_TO, 'Lookup', '', 'alias'=>'modelbStateLkp',
'on'=>"modelbStateLkp.code=t.state and modelbStateLkp.category='MachineState'")
This works fine when the models are accessed directly. Eg
ModelA::model()->with('stateLkp')->findAll()
ModelB::model()->with('stateLkp')->findAll()
However when we want to include the nested model B in the results for Model A, then problems ensue. Eg:
ModelA::model()->with('stateLkp', 'submodels.stateLkp')->findAll()
Everything is unambiguous in the final SQL query because of the aliases used in the relations - this is good! However in the left outer join generated to create the nested ModelB’s lookup code, ‘t.state’ will refer to the outermost table’s (ie ModelA’s) state field rather than what is wanted, which is the immediate parent’s (Model B’s) state field. Eg this would be the join:
LEFT OUTER JOIN `tbl_lookup` `modelbStateLkp` ON (modelbStateLkp.code=t.state and modelbStateLkp.category='MachineState')
This seems like a Yii problem, in that ‘t’ should refer to “this model” (which will actually have an autogenerated alias of something like t2 or t3) rather than “the outermost model” (t). However that would require string substitution by the appropriate generation method. This would be simple if if the table alias could be specified as a variable. Eg: ‘$t.state’, but I don’t know if this is possible.
Does anyone have a solution for this?