Yii 1.1 : Use findAllBySql and NOT EXISTS query


Here i am showing use case of NOT EXISTS query and how to run raw query in yii.

If any one want to display only those user name which are not in user role table, then have to run SQL query that with NOT EXISTS.

In UserRole model write a function named: getUserNamesNotExists

public function getUserNamesNotExists()
    $sql = 'SELECT id,username FROM tbl_users tbu WHERE  NOT EXISTS 
            (SELECT * FROM   tbl_user_role tbur WHERE  tbu.id = tbur.user_id) ';
    $data = UserModule::user()-&gt;model()-&gt;findAllBySql($sql);
    return $data;

Here shows how to use above function in view to display:

echo $form->dropDownList($model,'user_id',
        CHtml::listData(UserRole::model()->getUserNamesNotExists(), 'id', 'username'),
        array('empty'=>'Select Username'));

Leave a Reply

Your email address will not be published. Required fields are marked *