Related Models and Joins

I love yii! I have been using it for a few years now, and feel like i really know my way around the framework. However, there are a couple of areas where my understanding really breaks down. One of them is AR and joins/with/together. I’ve read the docs, and for some reason cannot seem to comprehend what exactly is going on. Perhaps if I describe a specific scenario, someone can explain that to me in YII/AR language.

So…lets imagine the scenario where i have a user table, a group table, and a group_mapping table. I want to run a query that would return all the User models that belong to a specific group (which is determined by their presence in the mapping table). I have defined a relationship between the two models and can run a query against groupMapping, and then look at the related model, but what I really want is the User model just for the ones in the specific group.

Any help would be appreciated.

Define a parameterized named scope in user model:

public function byGroupId($groupId)


  $this->dbCriteria->addCondition('EXISTS (SELECT * FROM  group_mapping WHERE user_id = ' . $this->tableAlias . '  .id AND  group_id = :group_id)');

  $this->dbCriteria->params[':group_id'] = $groupId;

  return $this;


And use it:

$users = User::model()->byGroupId($groupId)->findAll();

How do your models look like? This sounds like a MANY MANY relationship. If it is, then it should be as simple as grabbing a specific group then loading all related users


For speed I am mainly copying code here.

I have a similar case where I have Users that can be Supervisor of another User. The ‘ParentChild’ table defines the relations between users. One of the relations is supervising which is defined in a scope called ‘supervises’ on the ParentChild.

Here is an extract of the relations:

                /* Supervision */

                'supervisor' => array(

                        self::HAS_ONE,'User',array('parent_id'=>'entity_id'), // Supervisor is the parent

                        'through'=>'supervised_relation','joinType'=>'INNER JOIN'),

                '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'),


                        'through'=>'supervises_relation','joinType'=>'INNER JOIN'),

                'supervises_relation' => array(self::HAS_ONE, 'ParentChild', array('parent_id'=>'entity_id'),

                        'alias'=>'ssr'.$alias,'scopes'=>'supervises','joinType'=>'INNER JOIN'),

To find all the users supervised by another user I have this line of code:




In the above ‘getRealUser()’ provides the User instance of the currently logged user (as a sidenote, to clarify, there is also a getCurrentUser() to get the name of the user that the superuser is currently looking at).

The ‘select’ restriction is present because in this particular call I only want the ids and the names of the supervised users (to populate a dropdown).

‘$userDate’ provides the data for the dropdown.

‘supervisor_id’ is a 'virtual attribute scope automatically defined using my RelatedSearchBehavior extension with the following piece of configuration in ‘behaviors’:


[size=“2”]Basically that says that ‘supervisor_id’ is the entity_id in the relation supervisor (which is defined further above).[/size]

[size="2"]The RelatedSearchBehavior extensions will setup the CDbCriteria which would look something like (but better):[/size]











‘together’ is usefull when you are doing a selection in a CGridView to get the count right.

This reminds me again of the power of my RelatedSearchBehavior extension. With the right setup, I just have to do:



[size="2"]to get get the users having a selected supervisor. The resulting SQL query uses "three" tables (2xUser and 1xParentChild). You would have 1xUser, 1xGroup, 1xParentClhild.[/size]

You’ll also need to check up on RelatedSearchBehavior to fully “grasp” the above, but it is worthwhile to spend some time on it (you’ll save a lot more time later).

This is perfect! thank you…how cool is that, what a simple implementation.

I am new in YII. I am using the YII example. I use the sentence showed in the Post relation() topic


In the PostControler.php but I recieve this error.

Error 500

Undefined variable: post

In this page they say that I am too new to post.