Yii with join using CDbCriteria and CActiveDataProvider with custom Search

Question

Hi I am using Yii to create an Application

I have modified the search() in model and I have come up with a problem.

Users can log in as admins, managers, clients

When a user is logged as a manager, he can view clients only from the store they both belong. So far so good, I've managed to accomplish that.

Now the problem is when I try to prevent managers from viewing other managers from the same store (and therefore edit each other's accounts) in CGridView.

The relations

/**
 * @return array relational rules.
 */
public function relations()
{
    // NOTE: you may need to adjust the relation name and the related
    // class name for the relations automatically generated below.
    return array(
        'authitems' => array(self::MANY_MANY, 'Authassignment', 'authassignment(userid, itemname)'),
        'additionalContacts' => array(self::HAS_MANY, 'AdditionalContact', 'Client_Id'),
        'store' => array(self::BELONGS_TO, 'Store', 'Store_Id'),
        'user' => array(self::BELONGS_TO, 'User', 'User_Id'),
        'genericPoints' => array(self::HAS_MANY, 'GenericPoint', 'Client_Id'),
    );
}

The Custom model search

public function searchCustom()
{
    // Warning: Please modify the following code to remove attributes that
    // should not be searched.

    $criteria=new CDbCriteria;
    $criteria->addCondition('t.id !='.$this->id);
    $criteria->compare('t.Created',$this->Created,true);
    $criteria->compare('t.Updated',$this->Updated,true);
    $criteria->compare('t.Discount',$this->Discount,true);
    $criteria->compare('t.Discount_Type',$this->Discount_Type,true);
    $criteria->addCondition('t.Store_Id ='.$this->Store_Id);

    //$criteria->addCondition('t.id !='.$this->id);

    //GET FIELDS FROM USER IN SEARCH

    $criteria->with=array('user');
    $criteria->compare('user.id',$this->User_Id,true);
    $criteria->compare('user.First_Name',$this->First_Name,true);
    $criteria->compare('user.Last_Name',$this->Last_Name,true);
    $criteria->compare('user.Username',$this->Username,true);
    $criteria->compare('user.Email',$this->Email,true);
    $criteria->addCondition('user.Status = 1');

    $criteria->with=array('authitems');
    $criteria->compare('authitems.userid',$this->id,false);
    $criteria->compare('authitems.itemname','client',false);

    $criteria->together = true;

    $criteria->order = 't.Created DESC';
    return new CActiveDataProvider($this, array(
            'criteria'=>$criteria,
    ));
}

And this the error I am getting

CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'user.Status' in 'where clause'. The SQL statement executed was: SELECT COUNT(DISTINCT `t`.`id`) FROM `client` `t` LEFT OUTER JOIN `authassignment` `authitems_authitems` ON (`t`.`id`=`authitems_authitems`.`userid`) LEFT OUTER JOIN `authassignment` `authitems` ON (`authitems`.`itemname`=`authitems_authitems`.`itemname`) WHERE (((((t.id !=2) AND (t.Store_Id =1)) AND (user.Status = 1)) AND (authitems.userid=:ycp0)) AND (authitems.itemname=:ycp1))

I know it has something to do with the relations the gii set up for me but I can't pinpoint it.

Perhaps authitems' MANY_MANY relation stops the user relation from loading?

Answer 1

Instead of making another assignement for $criteria->with (which will override the previous one), you should simply try :

$criteria->with=array('user', 'authitems');