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.

ScaisEdge · Accepted Answer · 2017-03-13T17:51:52.663

45

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.

edited Mar 13 '17 at 17:51

answered Mar 13 '17 at 17:48

ScaisEdge

131,976
10
91
107

Yes, I've just read that and I've posted my answer just after yours. I'll accept yours anyway. Thanks! – Zlatan Omerović Mar 13 '17 at 17:51

score 29 · Answer 2 · answered Sep 12 '18 at 17:44

29

for this expression:

WHERE activated_at IS NULL

try this (it's working):

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

answered Sep 12 '18 at 17:44

user3551026

515
5
9

The "\yii\db\Expression" gave me an idea on how to use my 'use statement...'. Thanks – crafter Jan 25 '23 at 16:12

score 8 · Answer 3 · edited Jul 17 '19 at 23:12

8

$null = new Expression('NULL');

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

OR

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

edited Jul 17 '19 at 23:12

Laurenz Albe

209,280
17
206
263

answered Jul 17 '19 at 22:45

kociou

81
1
1

2

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. – Chrisvin Jem Jul 17 '19 at 23:23
`new Expression('NULL')` will work for both `->andWhere()` and `->andFilterWhere()`. That simple – NwosuCC Feb 04 '20 at 15:59
any `*where(['is not', 'field_name', new Expression('NULL')])` can be simplified to `*where(['is not', 'field_name', null])`. – Sasha MaximAL Dec 26 '22 at 12:21

score 2 · Answer 4 · answered Jun 25 '19 at 08:07

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

score 2 · Answer 5 · answered Jun 17 '20 at 20:38

2

// ...WHERE (`status` = 10) AND (`type` IS NULL) AND (`info` IS NOT NULL)
$query->where([
    'status' => 10,
    'type' => null,
])
->andWhere(['not', ['info' => null]]);

answered Jun 17 '20 at 20:38

jai3232

383
3
6

This would be a better answer if you explained how the code you provided answers the question. – pppery Jun 18 '20 at 00:53
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 – jai3232 Jun 18 '20 at 03:33

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

5 Answers5

Linked