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 ?

Hmm, I can't see anything else and this post shows you have the correct syntax http://stackoverflow.com/questions/18954600/how-to-use-isset-inside-addcondition-in-yii-criteria All I could possibly suggest is trying to put a static value like `$criteria->addCondition("user_city_id = 1");` or something similar to make sure it is working till that point. — The Humble Rat, Dec 12 '13 at 14:26
Are you sure there are any data are returned with your condition (user_city_id IS NOT NULL) in DB? — secretlm, Dec 12 '13 at 14:53
@Ultimate did you figure this out, I would be interested to know the solution. — The Humble Rat, Dec 13 '13 at 16:00
@TheHumbleRat: :( No I am still trying what is the issue. I'll post an answer whenever I fixed this. Thanks — J.K.A., Dec 14 '13 at 06:00
@Ultimate I am available on the addresses shown on my profile. If I can help I will. — The Humble Rat, Dec 14 '13 at 11:20
Can you show us the resulting SQL statement from logs? Also how `user_city_id` is defined? — Stan, Dec 14 '13 at 17:32
Are you sure that `$_GET['ulip']` is not set, i.e. that the `else` part of the condition is followed? You could `echo` some debug messages within each side of the `if` to be sure. Simply having `?ulip=` or `&ulip=` in the querystring (even if nothing follows the `=`) will result in the variable being set, in which case you may also want to test its content e.g. `if (isset($_GET['ulip']) and $_GET['ulip'] != '')` or else ensure that the parameter is removed from the querystring. — eggyal, Dec 15 '13 at 09:34
Please dont use $_POST or $_GET in Active record classes this breaks MVC, if you care — Cherif BOUCHELAGHEM, Dec 18 '13 at 07:59
@TheHumbleRat: Extremely Sorry for late reply. I've checked the answer of Hearaman and changed the code according to that, also cleared my old values from database and tried for new record and its working fine now. I think whatever Hearaman explained that is correct. BTW Thank you so much for your efforts in this. — J.K.A., Dec 18 '13 at 11:55
Your very welcome. I was as interested in the answer as you. Glad it's fixed. — The Humble Rat, Dec 18 '13 at 12:04

score 7 · Accepted Answer · edited May 23 '17 at 12:08

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.

score 4 · Answer 2 · answered Dec 14 '13 at 20:24

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,
        ),
    ));
}

can you please elaborate as to how your version is safer than the OP's — The Humble Rat, Dec 14 '13 at 21:20
Simple: No string concatenation to build query parts and safer parameter binding. There is no longer a bogus `compare()` for the `customer_basics_id` and `user_city_id`. I suspect latter one has been causing the troubles. — DaSourcerer, Dec 14 '13 at 21:22

score 0 · Answer 3 · answered Dec 14 '13 at 20:02

I've just created table in MySQL, model, crud, based on your fields and your search criteria, plus some test data. In my tests all works, both conditions generate some results.

If your need more information about my tests don't hesitate to ask. I suppose, error is connected with input data, hence I suggest to turn on debug mode, add log component to your config and after that look on sql queries that's yii generate.

score 0 · Answer 4 · answered Dec 18 '13 at 08:12

0

Maybe $criteria->addCondition("user_city_id <> '' "); solve the probleme because IS NOT NULL interpreted as true and NULL as false.

answered Dec 18 '13 at 08:12

Cherif BOUCHELAGHEM

1,126
2
14
21

IS NOT NULL not working in Yii Active Record

4 Answers4