Relational AR

I’m completely stuck with a query I’m trying to use to provide data to a CGridView widget. I’ve read through everything I can find on AR and have made some progress, but can’t quite get there. I’ll describe my aim at the bottom. My table structure is attached as an image (I’m not allowed to use links in posts yet).

User.php model:

public function relations()

{

    return array(

	'customers' => array( self::MANY_MANY, 'Customer', 'UserCustomer(userId, customerId)' ),

	'passReset' => array( self::HAS_ONE, 'PassReset', 'id' ),

	'userGroup' => array( self::BELONGS_TO, 'UserGroup', 'userGroupId' ),

	'userInfo' => array( self::HAS_ONE, 'UserInfo', 'id' ),

    );

}

public function search()

{

    $criteria = new CDbCriteria;


    $criteria->with = array('userInfo', 'userGroup');


    // Conditions to retrieve the data this user is allowed to view.

    if ( Yii::app()->user->checkAccess('manager') ) {

	

        // TODO: Restrict user data displayed such that a manager (logged-in user)

	// can only see data on users from the same customerId (organisation) as them

	

        $criteria->addInCondition( 'userGroup.name', array('staff', 'guest') );

    }


    $sort = new CSort();

    $sort->attributes = array(

    	'lastName' => array(

    	    'asc' => 'userInfo.lastName',

	    'desc' => 'userInfo.lastName DESC',

	),

	'firstName' => array(

	    'asc' => 'userInfo.firstName',

	    'desc' => 'userInfo.firstName DESC',

	),

	'userGroup' => array(

	    'asc' => 'userGroup.name',

	    'desc' => 'userGroup.name DESC',

	),

    );


    return new CActiveDataProvider( $this, array(

	'criteria' => $criteria,

	'sort' => $sort,

    ));

}

So in the search function, there’s a ‘TODO’ comment. That’s pretty much what I can’t get. The query works great so far - returns correct info for a user in the admin group (all info). But for a user in the manager group I want it to get all userInfo data for users who are in the guest or staff group who are also from the same customer (i.e. have the same customerId as the manager). At the moment a manager can only see staff and guests, but they can see them from every customer organisation (not just their own).

Any help would be greatly appreciated - this is my first project learning Yii and doubles as a school project!

If I got you correctly, I think you may use something like,

         $managerGroupId = User::model()->findByPk(Yii::app()->user->id)->userGroupId;

        $criteria->addCondition('user.userGroupId = '.$managerGroupId);

You may get $managerGroupId from any storage you want to (for example, you may want to get it from user states, e.g.

Yii::app()->user->groupId

)

Hope it’ll help.

Hey Yugene, thanks so much for the reply - you didn’t quite understand what I was after but you set me on the correct path! In case this post helps anyone else, I’ll explain what I end up doing.

I don’t really ‘feel’ right about this solution, it seems somehow dirty to me, but it works. Maybe someone can describe a better method.

Completing the code I listed in the first post, I now have:

// Conditions to retrieve the data this user is allowed to view.

    if ( Yii::app()->user->checkAccess('manager') ) {

	$customerId = User::model()->findByPk( Yii::app()->user->id )->customers[0]->id;

	$criteria->addCondition( '(SELECT `c`.`customerId` FROM `UserCustomer` `c` WHERE `c`.`userId` = `t`.`id`) = ' . $customerId );

	$criteria->addInCondition( 'userGroup.name', array('staff', 'guest') );

}

The weird part: customers[0] - restricting to one result on based on the MANY_MANY relationship (customers) is OK because in this case the relationship can kind of be considered to be BELONGS_TO. A user only has multiple customers if they’re an admin, and we’re only interested in managers in this part (managers belong to one customer organisation). Otherwise you could loop through the results at that point.

Then, I just add the condition that the record to be displayed must be a record of a user whose customerId is the same as the one I’ve retrieved in $customerId.

For completeness, an example query produced in Yii::trace is:

SELECT `t`.`id` AS `t0_c0`, `t`.`username` AS `t0_c1`,

`t`.`password` AS `t0_c2`, `t`.`userGroupId` AS `t0_c3`, `userInfo`.`id` AS

`t1_c0`, `userInfo`.`firstName` AS `t1_c1`, `userInfo`.`lastName` AS

`t1_c2`, `userInfo`.`email` AS `t1_c3`, `userInfo`.`phone` AS `t1_c4`,

`userGroup`.`id` AS `t2_c0`, `userGroup`.`name` AS `t2_c1`

FROM `User` `t` 

LEFT OUTER JOIN `UserInfo` `userInfo` ON (`userInfo`.`id`=`t`.`id`)

LEFT OUTER JOIN `UserGroup` `userGroup` ON (`t`.`userGroupId`=`userGroup`.`id`) 

WHERE (((SELECT `c`.`customerId` FROM `UserCustomer` `c` WHERE `c`.`userId` =

`t`.`id`) = 1) AND (userGroup.name IN (:ycp0, :ycp1)))

The last 1 in that query is $customerId retrieved in an earlier query as described in the code.