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

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

  1. Blog
  2. 15 November 2026
  3. 4 min read

Stefan Bogdanescu

Founder & Senior Architect

Handling NULL Conditions in Yii 2 ActiveRecord Queries

Filtering database records where a timestamp field is NULL versus having a value is a common requirement, often used to distinguish between "not yet activated" and "activated" states. In your scenario, the activated_at column defaults to NULL, and you need to apply specific logic in your Search Model based on the activated GET parameter.

The core issue lies in how Yii 2's query builder handles the assignment of NULL values within the $andFilterWhere array versus how it interprets them inside the chained method calls.

The Problem with Array Assignment vs. Method Calls

In your current implementation, you are mixing two distinct approaches to building the query: using the $andFilterWhere associative array and chaining methods like andFilterWhere() directly on the query object.

When you assign a value to the $andFilterWhere array key 'activated_at' => null, Yii 2 treats this differently depending on where the filter is applied. If you pass this array into andFilterWhere($andFilterWhere), the framework expects the array values to be non-null for standard operators or specific operators like 'not'. Passing null directly in the array context often results in the condition being ignored or treated as "no filter" rather than an explicit "is null" check, especially when combined with the andFilterWhere wrapper which is designed to only add conditions if the value exists.

Consequently, your code block:

$andFilterWhere['activated_at'] = null;

...does not generate the necessary SQL WHERE activated_at IS NULL clause in the generated query string because the filter array logic skips entries that evaluate to false or null in certain contexts.

The Correct Syntax for IS NULL and IS NOT NULL

To explicitly filter for NULL values, you must bypass the array assignment shortcut inside andFilterWhere and use the raw query builder methods which offer explicit operators. Yii 2 provides specific method signatures for this exact purpose.

Filtering for NULL:
Use the andFilterWhere method passing an operator array with 'null' as the key:

$query->andFilterWhere(['like', 'email', $this->email]); // Standard filter

// To filter WHERE activated_at IS NULL
$query->andFilterWhere(['activated_at', null]); 
// This generates: WHERE (activated_at IS NULL)

Wait, looking closely at your code again:

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

In your original snippet, you actually did use the correct syntax inside the chained call for the $activated === '0' case. However, the issue is likely that this line is being executed inside an if block that might not be entering as expected due to type coercion, or it is being overridden by the $andFilterWhere array logic if you were merging them incorrectly.

Let's look at the specific failure point in your code:

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

This syntax is correct for generating IS NULL. However, notice you also have this line inside the same block:

$andFilterWhere['activated_at'] = null;

If you are merging $andFilterWhere into the query later, that specific array assignment is useless. The solution is to ensure you only use the direct method call and remove the redundant array assignment.

Filtering for NOT NULL:
To filter where a value exists (i.e., IS NOT NULL), you must explicitly tell the query builder to negate the null check:

// Generates: WHERE (activated_at IS NOT NULL)
$query->andFilterWhere(['not', ['activated_at', null]]);

Handling Boolean Logic in Search Models

A subtle pitfall in your code is the condition checking the activated property:

if($this->activated || $this->activated === "0") {
    // ...
}

If $this->activated is an empty string "", the first part of the condition evaluates to false (falsy), but the second part "" === "0" is also false. However, if the form submits an unchecked checkbox or a blank value, $this->activated might be null or an empty string.

In Yii 2 Search Models, it is standard practice to treat an unset or empty GET parameter as "no filter applied" rather than forcing a specific state. If you want to allow filtering only when the user explicitly selects a radio button or checkbox in your GridView, you should check isset($_GET['activated']) before applying logic:

if (isset($_GET['activated'])) {
    if ($this->activated === '1') {
        // User selected "Activated" -> IS NOT NULL
        $query->andFilterWhere(['not', ['activated_at' => null]]);
    } else if ($this->activated === '0') {
        // User selected "Not Activated" -> IS NULL
        $query->andFilterWhere(['activated_at', null]);
    }
}

Summary of Implementation Patterns

When working with ActiveRecord and timestamp fields that default to NULL, remember these patterns:

  1. IS NULL: Use $query->andFilterWhere(['column_name', null]).
  2. IS NOT NULL: Use $query->andFilterWhere(['not', ['column_name', null]]).
  3. Array Filters: Avoid assigning null to keys in the $andFilterWhere associative array if you intend to filter for null values, as the framework may skip it. Instead, use the direct method calls shown above.

By separating the logic into explicit method calls and ensuring you only apply filters when the parameter is actually set, your GridView will correctly display records based on their activation status.

Our network