IS NOT NULL not working in Yii Active Record

Question

I am trying something like this:

public function search() {

        $criteria = new CDbCriteria;

        $criteria->compare('user_details_id', $this->user_details_id);
        $criteria->compare('user_type_id', $this->user_type_id);
        $criteria->compare('customer_basics_id', $this->customer_basics_id);
        $criteria->compare('user_address_id', $this->user_address_id);
        $criteria->compare('user_city_id', $this->user_city_id);
        $criteria->compare('is_active', $this->is_active);
        $criteria->compare('create_dttm', $this->create_dttm, true);
        $criteria->compare('update_dttm', $this->update_dttm, true);

        // if condition is working
        if (isset($_GET['ulip'])) {
            $criteria->addCondition(
                    "customer_basics_id=" . CustomerBasics::getCustomerBasicsId(Yii::app()->session['user_id']), "AND"
            );
            $criteria->addCondition("user_city_id IS NULL");
        // else condition is working
        } else {
            $criteria->addCondition(
                    "customer_basics_id=" . CustomerBasics::getCustomerBasicsId(Yii::app()->session['user_id']), "AND"
            );
            $criteria->addCondition("user_city_id IS NOT NULL");
        }


    return new CActiveDataProvider($this, array(
        'criteria' => $criteria,
        'pagination' => array(
            'pageSize' => 10,
        ),
    ));
}

Here the issue is if condition is working fine and showing results according to the condition but else part is not working and it returns nothing. I think IS NOT NULL is not working here.

What is the issue ?

Answer 1

The Main reason.

In your database table column(user_city_id), you have Empty values not NULL values. So your query is unable to operate "IS NULL" and "IS NOT NULL" on the corresponding column.

      1. NULL is Special Data Type.
      2. Where as Empty means a string/value which is empty.

You can read more here

No need to add operator for first addCondition

For your information, When you are adding a condition to your criteria, no need to add "AND" operator becoz by default "AND" is the operator in addConditon. And no use if you add operation for first addCondition, You should add this for your next addConditions if you have any.

     //AND is not required here as AND is default operation. 
     //This operator (AND) wont help in first condition.
     $criteria->addCondition('condition1=1','AND');

     //This bellow condition will be concatenate with above condition so, here operator is required.
     $criteria->addCondition('condition2=1','OR');

The Solution is

I dont like to disturb default search method in my model. As i'm using MVC Framework, i should follow at least some MVC rules. Otherwise there is no meaning using this MVC. So, accessing $_GET stuff in my Model is not good here. so i'm creating a new method in my Model with two parameters.

        function yourModelMethod($isParamExist,$customer_basics_id)
        {
            $criteria = new CDbCriteria;
            $criteria->condition = "customer_basics_id=$customer_basics_id";        
            if($isParamExist)
            {            
                $criteria->condition='TRIM(user_city_id) =""';
            }
            else
            {
                $criteria->condition='TRIM(return_date) !=""';
            }

            return new CActiveDataProvider($this, array(
                'criteria' => $criteria,
            ));
        }

Now, I'm using this model method from my controller

        function actionYourController()
        {
            $model=new MyModel();        
            $isParamExist=isset($_GET['ulip']);
            $customer_basics_id=CustomerBasics::getCustomerBasicsId(Yii::app()->session['user_id']);        
            $activeData=$model->yourModelMethod($isParamExist,$customer_basics_id);
            $this->render('myView',array('activeData'=>$activeData));
        }

I hope, it will definitely solve your problem.

Answer 2

I'm not really sure this will really take care of your problem. But at least it adheres to safe coding practices:

public function search()
{    
    $criteria = new CDbCriteria;

    $criteria->compare('user_details_id', $this->user_details_id);
    $criteria->compare('user_type_id', $this->user_type_id);
    $criteria->compare('user_address_id', $this->user_address_id);
    $criteria->compare('is_active', $this->is_active);
    $criteria->compare('create_dttm', $this->create_dttm, true);
    $criteria->compare('update_dttm', $this->update_dttm, true);

    $criteria->compare('customer_basics_id', CustomerBasics::getCustomerBasicsId(Yii::app()->session['user_id']));

    if(isset($_GET['ulip']))
        $criteria->addCondition('user_city_id IS NULL');
    else
        $criteria->addCondition('user_city_id IS NOT NULL');

    return new CActiveDataProvider($this, array(
        'criteria' => $criteria,
        'pagination' => array(
            'pageSize' => 10,
        ),
    ));
}