CODEWITHSUNDEEP
phpsqlactiverecordyii2
user1724416
user1724416

Reputation: 954

How to find ActiveRecords which have attribute either equal to value or be null in Yii2

I basically have an active query and I either want to filter that column if it matched the ID or is null

MyTable::find()->andFilterWhere(['in', 'org_id', [null, $org_id]])->all();

currently using this, works fine for the $org_id, but it will not return null values. I tried this to see if I could get any null results, but no avail

MyTable::find()->andFilterWhere(['in', 'org_id', null])->all();

Any tips?

Upvotes: 4

Views: 9753

Answers (4)

Sơn Đặng
Sơn Đặng

Reputation: 11

If you are using a filter of multiple values, the following query will result in cell with null or values in an array:

$query->andWhere(['or', ['in', 'column', $value_array], ['column' => null]]);

Upvotes: 1

hijarian
hijarian

Reputation: 2228

To completely answer this question, let's reiterate what we really want.

We need to get all records from some table which have some specific 'field' to be equal either to given $value or to null.

Given that definition, we can translate it directly into Query call:

$query->andWhere(['or', ['field' => $value], ['field' => null]]);

This is somewhat suboptimal in cases when $value is null, because it gives the SQL WHERE (field IS NULL) OR (field IS NULL). You can mitigate it by wrapping the andWhere call with if statement:

if ($value === null)
    $query->andWhere(['field' => null]);
else
    $query->andWhere(['or', ['field' => $value], ['field' => null]]);

or depending on your tastes, using ternary operator:

$query->andWhere(
    $value === null
        ? ['field' => null]
        : ['or', ['field' => $value], ['field' => null]]
);

In any case, using andFilterWhere completely defeats the purpose of your query, because it exists solely to remove pieces like ['field' => null] from the queries.

Upvotes: 0

Leviathan
Leviathan

Reputation: 1

To reinforce arogachev's answer, this is a good reference, if you wanted to just query for null results: https://github.com/yiisoft/yii2/issues/6788

$query->andfilterWhere('org_id' => null)

This query doesn't work because filterWhere removes empty params.

For null results, use:

$query->andWhere('org_id' => null)

Upvotes: 0

arogachev
arogachev

Reputation: 33538

Correct you query like that:

MyTable::find()
   ->andFilterWhere(['in', 'org_id', $this->org_id])
   ->orWhere(['org_id' => null])
   ->all();

You can specify IS NULL condition with array also, orWhere is enough because this condition does not depend on attribute value.

Upvotes: 5

Related Questions