IS NOT NULL not working in Yii Active Record
Solution 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.
Solution 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,
),
));
}
Comments
-
J.K.A. almost 2 years
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 thinkIS NOT NULL
is not working here.What is the issue ?