Complex Active Record Relation

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.

Could you please enlighten me to make this work?


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()



	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) {






                                        'joinType'=>'INNER JOIN',








        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’.