How to do IS NULL and IS NOT NULL with Yii 2 ActiveRecord?

Question

I have a table which has a field `activated_at` timestamp NULL DEFAULT NULL, which means that it can contain a timestamp or it can be null and it's null by default.

I have another [gii-generated] search model with a following configuration in the search() method:

public function search($params) {     $query = User::find();      // add conditions that should always apply here      $this->load($params);      if (!$this->validate()) {         // uncomment the following line if you do not want to return any records when validation fails         // $query->where('0=1');         return $dataProvider;     }      $andFilterWhere = [         'id' => $this->id,         'status' => $this->status,         'role' => $this->role,         'created_at' => $this->created_at,         'updated_at' => $this->updated_at,         'completed_files' => $this->completed_files,         // 'activated_at' => null,     ];      if(!isset($_GET['deleted'])) {         $query->where(['deleted_at' => null]);         $andFilterWhere['deleted_at'] = null;     } else if($_GET['deleted'] === 'true') {         $query->where(['not', ['deleted_at' => null]]);     }      // grid filtering conditions     $query->andFilterWhere(         $andFilterWhere     );      $query->andFilterWhere(['like', 'first_name', $this->username])         ->andFilterWhere(['like', 'auth_key', $this->auth_key])         ->andFilterWhere(['like', 'password_hash', $this->password_hash])         ->andFilterWhere(['like', 'password_reset_token', $this->password_reset_token])         ->andFilterWhere(['like', 'email', $this->email])         ->andFilterWhere(['like', 'first_name', $this->first_name])         ->andFilterWhere(['like', 'last_name', $this->last_name]);      if($this->activated || $this->activated === "0") {         #die(var_dump($this->activated));         if($this->activated === '1') {             // this doesn't filter             $query->andFilterWhere(['not', ['activated_at' => null]]);         } else if($this->activated === '0') {             // this doesn't either             $query->andFilterWhere(['activated_at', null]);         }     }      $dataProvider = new ActiveDataProvider([         'query' => $query,     ]);      return $dataProvider; } 

Yes, I have set the activated property in my class:

public $activated; 

And my rules() method is as following:

public function rules() {     return [         [['id', 'status', 'role', 'created_at', 'updated_at', 'completed_files'], 'integer'],         ['activated', 'string'],         [['username', 'first_name', 'last_name', 'auth_key', 'password_hash', 'password_reset_token', 'email', 'deleted_at', 'completed_files', 'activated_at'], 'safe'],     ]; } 

What I was trying to set in the search() method is to filter on field activated_at depending on the $activated value (see above code):

if($this->activated || $this->activated === "0") {     #die(var_dump($this->activated));     if($this->activated === '1') {         // this doesn't filter         $query->andFilterWhere(['not', ['activated_at' => null]]);     } else if($this->activated === '0') {         // this doesn't either         $query->andFilterWhere(['activated_at', null]);         $andFilterWhere['activated_at'] = null;     } } 

I use it with GridView - every other filter works except this one.

What am I doing wrong here?

Aand how to properly do this sort of queries:

IS NULL something IS NOT NULL something 

With Yii 2's ActiveRecord query builder?

---

EDIT: Line: if(!isset($_GET['deleted'])) is used for something else and this works normally.

Answer 1

If i understand right you can use andWhere

 ->andWhere(['not', ['activated_at' => null]]) 

but andFilterWhere in execute where the related value is not null

from doc http://www.yiiframework.com/doc-2.0/yii-db-query.html

andFilterWhere() Adds an additional WHERE condition to the existing one but ignores empty operands.