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.

Solution 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.

Solution 2

for this expression:

WHERE activated_at IS NULL

try this (it's working):

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

Solution 3

$null = new Expression('NULL');

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

OR

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

Solution 4

this solution check if column_name is empty or NULL

WHERE (LENGTH(`column_name`) > 0)

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

---

Another variant - check only for NULL

WHERE column_name IS NOT NULL

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

View more solutions

Comments

[Zlatan Omerović]

Yes, I've just read that and I've posted my answer just after yours. I'll accept yours anyway. Thanks!

[Chrisvin Jem]

Hi, welcome to StackOverflow. Avoid answering with just code, try to provide an explanation as to what the issue is and how your code fixes said issue.

[NwosuCC]

new Expression('NULL') will work for both ->andWhere() and ->andFilterWhere(). That simple

[pppery]

This would be a better answer if you explained how the code you provided answers the question.

[jai3232]

This answered a query which filter for NULL and NOT NULL column with different column name as asked in the question where status is 10 and type is NULL and info is NOT NULL. I hope you understand. TQ