CODEWITHSUNDEEP
phpyii2
Felipe Guzman
Felipe Guzman

Reputation: 55

How to filter a data when it's null?

I try to make a new query search in PlanificacionSearch but I don't know how to filters results where the value 'asistencia' is only null. In the code below whit this condition, it returns all the elements, ignoring the null condition on that value.

In SQL, this is the desired query:

SELECT asistencia FROM gestion_talleres.planificacion
WHERE rutProfesor = <session_id> AND asistencia IS NULL

And this is the query I wrote in Yii 2:

$query
            ->andFilterWhere(['asistencia' => null])
            ->andFilterWhere(['like', 'rutProfesor', Yii::$app->user->identity->rutProfesor]);

The condition for 'rutProfesor' is already working, but not the 'asistencia' one. I need help.

Upvotes: 1

Views: 915

Answers (2)

Marcos
Marcos

Reputation: 1378

Try this way:

$query
    ->andFilterWhere(['=', 'asistencia', null])
    ->andFilterWhere(['like', 'rutProfesor', Yii::$app->user->identity->rutProfesor]);

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133400

When building WHERE conditions based on input from end users, you usually want to ignore those input values, that are empty. For example, in a search form that allows you to search by username and email, you would like to ignore the username/email condition if the user does not enter anything in the username/email input field. You can achieve this goal by using the filterWhere() method:

http://www.yiiframework.com/doc-2.0/guide-db-query-builder.html#filter-conditions

andFilterWhere(['asistencia' => null]) work only if the value is not null

if you need check for null condition you shoud use andWhere

andWhere(['asistencia' => null])

that is always executed or where with multiple value eg: 

 // ...WHERE (`status` = 10) AND (`type` IS NULL) AND (`id` IN (4, 8, 15))
 $query->where([
   'status' => 10,
   'type' => null,
   'id' => [4, 8, 15],
]);

http://www.yiiframework.com/doc-2.0/guide-db-query-builder.html

Upvotes: 4

Related Questions