php 如何使用 Yii 2 ActiveRecord 做 IS NULL 和 IS NOT NULL？

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

提问by omerowitz

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 nulland it's nullby default.

我有其中有一个字段的表`activated_at` timestamp NULL DEFAULT NULL，这意味着它可以包含时间戳，也可以是null和它的null默认。

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

我有另一个 [gii-generated] 搜索模型，方法中具有以下配置search()：

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 activatedproperty in my class:

是的，我已经activated在我的班级中设置了属性：

public $activated;

And my rules()method is as following:

我的rules()方法如下：

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_atdepending on the $activatedvalue (see above code):

我试图在search()方法中设置的是activated_at根据$activated值过滤字段（见上面的代码）：

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.

我将它与GridView- 除了这个过滤器之外的所有其他过滤器都有效。

What am I doing wrong here?

我在这里做错了什么？

Aand how to properly do this sort of queries:

A以及如何正确执行此类查询：

IS NULL something
IS NOT NULL something

With Yii 2's ActiveRecordquery builder?

使用 Yii 2 的ActiveRecord查询构建器？

---

---

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

编辑：行：if(!isset($_GET['deleted']))用于其他用途，这正常工作。

回答by scaisEdge

If i understand right you can use andWhere

如果我理解正确，您可以使用 andWhere

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

but andFilterWhere in execute where the related value is not null

但是 andFilterWhere in execute where 相关值不为空

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

来自文档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.

andFilterWhere() 向现有条件添加一个额外的 WHERE 条件，但忽略空操作数。

回答by user3551026

for this expression:

对于这个表达式：

WHERE activated_at IS NULL

try this (it's working):

试试这个（它正在工作）：

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

回答by kociou

$null = new Expression('NULL');

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

OR

或者

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

回答by Zlocorp

this solution check if column_nameis empty or NULL

此解决方案检查是否column_name为空或 NULL

WHERE (LENGTH(`column_name`) > 0)

WHERE (LENGTH(`column_name`) > 0)

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

---

---

Another variant - check only for NULL

另一个变体 - 仅检查 NULL

WHERE column_name IS NOT NULL

WHERE column_name IS NOT NULL

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