Search code examples
phpyiiphpactiverecord

Yii 1.x, query in query DB selection

I have such sql query

SELECT * FROM tbl_role WHERE 
tbl_role.id NOT IN (
     SELECT tbl_user_role.roleID FROM tbl_user_role 
     WHERE tbl_user_role.userID = 35
)
AND
tbl_role.id NOT IN (
     SELECT tbl_user_role_request.roleID FROM tbl_user_role_request 
     WHERE tbl_user_role_request.userID = 35 AND tbl_user_role_request.dateEnd IS NULL)

What is the best solution to make it in Yii-like code. How can i do this query using Yii 1.x ?

Solution

  • Assumptions:

    • tbl_role has a model named Role
    • tbl_user_role has a model named UserRole
    • tbl_user_role_request has a model named UserRoleRequest

    Note that this will do 3 seperate queries but I believe this is the closest you can get without creating a total mess.

    <?php

$userId = 35;

$roles = UserRole::findAllByCondition(array('userId' => $userId));
$roleIds = array_values(CHtml::listData($roles, 'roleId', 'roleId');

$roleRequests = UserRoleRequest::findAllByCondition(array('userId'=>$userId, 'dateEnd'=>null));
$roleRequestIds = array_values(CHtml::listData($roleRequest, 'roleId', 'roleId'));

$criteria = new CDbCriteria();
$criteria->addNotInCondition('id', $roleIds);
$criteria->addNotInCondition('id', $roleRequestIds);

$roles = Role::findAll($criteria);