如何使用Yii 2 ActiveRecord执行IS NULL和IS NOT NULL? [英] How to do IS NULL and IS NOT NULL with Yii 2 ActiveRecord?
问题描述
我有一个表,其中有一个字段 activated_at`时间戳NULL默认NULL
,这意味着它可以包含一个时间戳,也可以是 null
,默认情况下为 null
。
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.
我还有另一个[gii生成] search()
方法中具有以下配置的搜索模型:
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;
我的 rules()
方法如下以下内容:
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'],
];
}
我试图在搜索中设置的内容( )
方法是根据 $ activated
值对字段 activated_at
进行过滤(请参见上文)代码):
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;
}
}
我在 GridView中使用它
-除此以外的所有其他过滤器都有效。
I use it with GridView
- every other filter works except this one.
我在这里做错了什么?
A以及如何正确执行这种查询:
Aand how to properly do this sort of queries:
IS NULL something
IS NOT NULL something
使用Yii 2的 ActiveRecord
查询生成器?
With Yii 2's ActiveRecord
query builder?
编辑:行: if(!isset($ _ GET ['deleted ']])
用于其他功能,并且正常工作。
Line: if(!isset($_GET['deleted']))
is used for something else and this works normally.
推荐答案
如果我理解正确您可以使用andWhere
If i understand right you can use andWhere
->andWhere(['not', ['activated_at' => null]])
但是andFilterWhere在执行时相关值不为空
but andFilterWhere in execute where the related value is not null
来自doc http://www.yiiframework.com/doc -2.0 / yii-db-query.html
andFilterWhere()向现有的
添加附加的WHERE条件,但忽略空操作数。
andFilterWhere() Adds an additional WHERE condition to the existing one but ignores empty operands.
这篇关于如何使用Yii 2 ActiveRecord执行IS NULL和IS NOT NULL?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!