I have the following tables on my DB (see attached image):
As you can see, employe is associated with section, section is associated with retail store. Plus, I have another table for employees that manages the retail (relation 1:N, one employe can be manager of many retail stores). Does it make sense?
What I am trying: when I get an employe, can get the managers of that store. The only way to get the managers is checking if the employe associated with the retail store (retail_management).
Example code:
$employe = Employe::model()->findByPk(10);
var_dump($employe->managers); // List of available manager on the store that employe works
Unfortunately, I cannot change the database layout. I have no ideia how I can configure an relation to work in this case.
Here is an extract of similar a code for a similar cae where a user has a supervisor.
The way I do this is:
Set up the relations, refering to scopes;
Define the scopes;
Define a parametrized scope.
protected static $aliasId=0;
/**
* Provides a unique alias for a field.
*/
private function aliasId() {
return 'eid'.self::$aliasId++;
}
/**
* @return array relational rules.
*/
public function relations()
{
$alias=$this->aliasId();
return array(
/* .. */
'supervisors' => array(
self::HAS_MANY,'User',array('parent_id'=>'entity_id'), // Supervisor is the parent
'through'=>'supervised_relation','joinType'=>'INNER JOIN'),
'supervised_relation' => array(self::HAS_MANY, 'ParentChild', array('child_id'=>'entity_id'),
'alias'=>'sdr'.$alias,'scopes'=>'supervises','joinType'=>'INNER JOIN'),
);
}
/**
* Scope that checks if the provided 'id' is the supervisor
*
* @param integer $id
* @return Employe
* Returns the same entity to enable chaining.
*/
public function supervisor($id) {
$alias=$this->aliasId();
$this->getDbCriteria()->mergeWith(
array(
'with'=>array(
'supervisors'=>array(
'joinType'=>'INNER JOIN',
'select'=>array(),
'condition'=>"`supervisors`.`entity_id`=:$alias",
'params'=>array(":$alias"=>$id),
)
)
)
);
return $this;
}
The ‘supervised_relation’ is much like your retail management, but you have to set up some more relations.
With the above, I would do ‘Employe::model()->supervisor($supervisor_id)->findAll()’ to find Employees with a given supervisor, but you can also do ‘$model->supervisors’.