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

Question

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

I have another [gii-generated] search model with the 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 set the activated property to my class:

 public $activated; 

And my rules() method looks like this:

 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 tried to set in the search() method is a filter in the activated_at field depending on the value of $activated (see code above):

 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 a GridView - every other filter works except this one.

What am I doing wrong here?

A and how to correctly execute such requests:

 IS NULL something IS NOT NULL something 

With Yii 2 ActiveRecord Query Designer?

---

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

Answer 1

If I understand correctly, you can use andWhere

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

but also FilterWhere in execution, where the bound value is not null

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

and FilterWhere () Adds an additional WHERE clause to an existing one, but ignores empty operands.

Answer 2

for this expression:

 WHERE activated_at IS NULL 

try this (this works):

 ->andWhere(['is', 'activated_at', new \yii\db\Expression('null')]), 

Answer 3

this solution checks if column_name empty or NULL

WHERE (LENGTH('column_name') > 0)

 ->andWhere(['>', 'LENGTH(column_name)', 0]) //check if empty or null 

---

Another option is to check only for NULL

WHERE column_name IS NOT NULL

 ->andWhere(['IS NOT', 'column_name', null]); // check on null 

Answer 4

 $null = new Expression('NULL'); $query->andFilterWhere(['is not', 'asp_id', $null]); 

OR

 $query->andFilterWhere(['is', 'asp_id', $null]);