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?

Hi

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